Cloud Quest #9: Crane Leasing

[AYXAcademy]

Hi Maveryx,

We posted the solution JSON file to Cloud Quest #8. Check it out and let us know what you think! Send suggestions to academy@alteryx.com or leave a comment below!

Let’s dive into this week's quest!

1. Upload the provided Cloud Quest 9 - Start File.json file into your Analytics Cloud library.
2. The necessary datasets are pre-populated in the Text Input tools.

For more detailed instructions on how to import and export Designer Cloud workflow files, check out the pinned article Cloud Quest Submission Process Update.

Scenario:

In this week’s quest, a general contractor needs your assistance assessing his business using date-time analysis.

Bob, who had been working as a home builder, decided to transition his business towards leasing construction equipment. Among other equipment, he has two cranes he has been leasing for a while. He wants to get a better idea how his crane leasing business has been going.

Create a workflow that will show how many days both of his cranes have been leased at the same time during the years 2016-2018, the only years where he has a full set of data for both cranes.

Hint: In Designer Desktop, the Generate Rows tool would be the most efficient way to tokenize records within a date range. Unfortunately, the Generate Rows tool is not yet available in Designer Cloud (coming soon in 2024!), but you can still solve this quest thinking outside the box with a cartesian join from the Append Columns tool.

スポイラ

A combination of the Summarize, Filter, Append Columns, Formula, and Sample tools should solve your problem, but not necessarily in this sequence.

If you find yourself struggling with any of the tasks, feel free to explore these interactive lessons in the Maveryx Academy for guidance:

• Getting Started with Designer Cloud
• Building Connections in Designer Cloud
• Building Your Workflow in Designer Cloud

Once you have completed your quest, go back to your Analytics Cloud library.

• Download your workflow solution file.
• Include your JSON file and a screenshot of your workflow as attachments to your comment.

Here’s to a successful quest!

 

[alexnajm]

I'm a bit stuck to be honest... not having the Generate Rows tool is tough! Looking out for the solution in a couple of weeks.

[Qiu]

Generate Rows tool is still not available, huh...
Have to go through a lot tools and not 100% sure doing it right way, but the results are matching. 😁

スポイラ

[Qiu]

I do wondering how to use the "Sample" tool in the hint provided.

[Shelbey]

This one was difficult without Generate Rows...

I started by creating a bunch of flag fields and different Filters to find the rows where the Maximus crane dates fell within the Tiny Tim crane dates, and then reversed it to find when Tiny Tim fell within the Maximus dates. After reconfiguring over and over I gave up and peaked at a spoiler... Thank you @Qiu for the hint I needed to solve this with 2 formulas and a filter instead of what was turning into 5+ formulas and filters and a union.

スポイラ

[Qiu]

@Shelbey 
Glad to be hintful. 😂

I think I learned this kindl cluster matching rather than individual matching in one of the challenges of AoC this year.

[AkimasaKajitani]

My solution!

スポイラ

 

As @Qiu said, I've done something similar to this quest at the AoC.The generate row way is very easy, but when there are too much data, that way is too late. In that case, this way is very useful! But this way is very confused to our brain!

[RWvanLeeuwen]

Here we go

スポイラ

I'm glad for the many Trifacta users that the Trifacta experience still exists because generating rows (or joining using inequality operators) is such an important capability to have

[RWvanLeeuwen]

Debug time!

I did not notice my mistake as the data previews were all empty, but I fixed a missing filter, because we need to use only the rows where the status="Leased" (and I really really hope that this filter is appropriate because I cannot appropriately verify the data quality and case sensitivity without datapreviews

スポイラ

[mceleavey]

This was a bit a pain without range matching functionality or Generate Rows available at the time of completion (Hello future people, and if you have this functionality by the time you read this, please realise how lucky you are. Now go and enjoy your flying car and multi-clause Filter Tool.)

スポイラ

I have no idea if I've done this the right way as I was reduced to using the Append tool, which hurts my pancreas.

I converted the dates to the correct format then parsed out the Year to make filtering easier. I then filtered out those that fell completely out of the date range. I then split the cranes into two streams and appended all back together (yeesh). I then created a Joint Start and Joint End date field based on the offset, thereby creating the start and end of the joint period of lease. I then calculated the days between (adding 1), and then summarised.