Cloud Quest #23: Filling in the Blanks

[AYXAcademy]

Hi Community,

We posted the solution JSON file to Cloud Quest #22. 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. Download the provided JSON file containing your starting data and workflow files.
2. Upload the provided Cloud Quest 23 Start.json file into your Analytics Cloud library.
3. All necessary datasets are contained within Text Input tools in the workflow.

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:

This quest was adapted from a submission by @Maskell_Rascal and @mceleavey. Thank you both!

For this week's quest, we have provided a list of transaction data including the Customer, Date, and Amount. Since not every customer makes a purchase every day, they only have entries on dates when they made a purchase.

Your task is to include rows for customers in the dataset with an [Amount] of 0 if they have missing dates after their first purchase. They should not have entries for dates prior to their first purchase. Assume a customer’s first purchase date is their earliest entry in the dataset. 

Hint: Summarize the Min_Date and Max_Date for use in the Generate Rows tool.

Spoiler

A combination of the Join, Generate Rows, Summarize, Append Fields, and Formula 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 Alteryx 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 as an attachment to your comment.

Here’s to a successful quest!

[alexnajm]

@AYXAcademy can you upload the file?

[AYXAcademy]

@alexnajm  We clearly didn't get enough coffee this morning! Thank you Alex. The start file has been posted.

[alexnajm]

Thank you @AYXAcademy ! Always love a good cup of joe 😊☕

[alexnajm]

Done!

Spoiler

[Qiu]

A cup of coffee is necessary in the morning. 

Spoiler

[RWvanLeeuwen]

Quick and easy to do. Maybe some day I will learn to overwrite an original column when generating dates from a range as to conserve credits

Spoiler

[ggruccio]

Had to think about this one a bit!  

Spoiler

Pulled every combination of customers and dates and then found the missing combinations.  Did a quick filter on first purchase date to remove any that may have occurred before.  

[JeffF]

Spoiler

[AkimasaKajitani]

My solution!

Spoiler