topic Betreff: Cloud Quest #2: Consumer Purchase Aggregation in Cloud Quests

Cloud Quest #2: Consumer Purchase Aggregation

AYXAcademy — Mon, 12 May 2025 22:58:41 GMT

Hi Maveryx,

Thanks for submitting your solutions for our first official Cloud Quest! In the video solution we posted, Alteryx Chief Evangelist, Joshua Burkhow (@joshuaburkhow) will guide you through the workflow (scroll down the page to locate the video).

Thank you, Josh, for the exceptional guidance!

For this week’s quest, you are taking on the role of a restaurant manager. You want to review customer purchase behavior to decide whether your restaurant should offer a meal deal that would add a side and drink to a pizza or burger purchase. To make this decision, you need to study recent transactions to determine the potential impact it could have.

The Point of Sale dataset includes the ticket-level information, and the two lookup tables categorize items into higher-level food types.

Your task is to determine the percentage of orders since July 1, 2013, that include the Food category (Pizza or Burger) paired with a Side AND Drink. To accomplish this, you need to determine the total number of orders placed within the specified timeframe and identify those that meet the criteria for the potential meal deal you are considering for your menu.

Hint 1: Remember to combine the dataset with the lookup tables and filter by date.

Hint 2: Remember to account for dates after June 30, 2013 to include July 1, 2013 in the output.

Your final result should look like the following image:

Hint: A combination of Join, Filter, Summarize, Cross Tab, Append Columns, and Summarize tools should solve your problem, not necessarily in this sequence.

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

Once you have completed this quest, capture a screenshot of your finalized workflow in Designer Cloud and attach the image of your solution to a comment on this post.

Here’s to a successful quest!

SOLUTION

We want to hear from you!

As we introduce our Cloud Quests for the first time, we value your input. Share your thoughts on the clarity of instructions, the provided dataset, the expected output, and any suggestions to enhance your experience.

Thank you!

Re: Quest #2: Consumer Purchase Aggregation

Treyson — Wed, 21 Feb 2024 16:07:14 GMT

I tried using as many tools as I could because there is a lot of function changes that have been made to some tools and I want to know what they are.

Specifically on the cross tab tool, the ability to select which new columns you are creating is nice, however I think I would like an "add all" option or it defaults to making a column for every value and we can chip away at what we want returned.

Also I learned that the Dynamic Rename tool doesn't have the "Take Column Name from First Row" function and that has been moved to the dataset creation section. I think there might be reasons why I want Dynamic Rename to still have that option.

Re: Quest #2: Consumer Purchase Aggregation

ScottMcV — Wed, 21 Feb 2024 17:40:19 GMT

Tried to use a simple set of tools to keep it clean. An explicit "add all" would be nice on the Crosstab (but not automatic, as that might add all the orderids, as an example, given the identity usually comes first in the data).

Re: Quest #2: Consumer Purchase Aggregation

Treyson — Wed, 21 Feb 2024 17:45:15 GMT

@ScottMcV Super great point on selecting the wrong field as the header. I hadn't thought of that. But a select all would be super useful.

Re: Quest #2: Consumer Purchase Aggregation

geoff_zath — Wed, 21 Feb 2024 20:35:19 GMT

Here is my solution. I tried this without the Cross Tab tool. One issue I found was not being able to use the Join tool to join by record position. The Append tool works in this case, but if I have more than one set of rows that I want to join manually, the Append tool is not the ideal way to do it.

Comment on the solution, I think the posted solution is for dates after July 1st. The filter tool doesn't have a >= function so we need to set dates for after June 30th instead.

@Treyson Note on the Dynamic Rename tool, I'm guessing there's a limitation here with how sampling works in AAC (i.e. the first row being sampled might not actually be the first row). That's why that function hasn't been added yet.

Re: Quest #2: Consumer Purchase Aggregation

geoff_zath — Wed, 21 Feb 2024 20:57:25 GMT

My above comment about sampling got me thinking, since the input PoS dataset is larger than 10 MB, what you are seeing in the workflow is only a sample (10 MB max). (Correct me if wrong) You'll need to add an Output Data tool and then select Run Job to generate a CSV with the results from the entire dataset. See the difference in the Sample Results window (sampled data) versus the CSV output when the job is run on the entire dataset.

Sampled Data:

Job Output:

Re: Quest #2: Consumer Purchase Aggregation

bradshaw — Wed, 21 Feb 2024 23:42:46 GMT

To get these results I filtered out 1 July 2013, but I argue that we should include it too.

This was a good one.

Re: Quest #2: Consumer Purchase Aggregation

AYXAcademy — Fri, 23 Feb 2024 16:35:21 GMT

Hi @geoff_zath, thanks for your comments!

Using the Append Columns tool instead of the Join tool has proved to be very effective in this scenario. Perhaps in the future, we'll see a configuration for joining by position. 😉 Additionally, the Union tool could also be used, however with a bit more postprocessing required.

Your point about the absence of the >= function for filtering dates is not accurate. The availability of operators such as >= depends on the data type being manipulated. For numerical data types, the >= operator will indeed be available. Does it make sense?

Thanks for bringing this to my attention!

Once again, thank you for resolving this week's quest!

SylviaP

Re: Quest #2: Consumer Purchase Aggregation

RobertW — Thu, 22 Feb 2024 18:04:36 GMT

Here's my solution without crosstab

Re: Quest #2: Consumer Purchase Aggregation

SGolnik — Thu, 22 Feb 2024 20:16:21 GMT

It is fun to see that even in the cloud world, we all still solve the problems using different tools.

Re: Quest #2: Consumer Purchase Aggregation

andrewgu — Fri, 23 Feb 2024 01:10:30 GMT

Solved during the AU-Syd weekly challenge hour!

This one was surprisingly a tricky one, especially with restructuring/counting the data to account for meals.

Re: Quest #2: Consumer Purchase Aggregation

bradshaw — Fri, 23 Feb 2024 01:47:33 GMT

There's an AU-Syd weekly challenge hour!?

Re: Quest #2: Consumer Purchase Aggregation

AkimasaKajitani — Fri, 23 Feb 2024 09:55:59 GMT

My solution!

I saw this challenge as Weekly challenge #8.

Re: Quest #2: Consumer Purchase Aggregation

Ladarthure — Fri, 23 Feb 2024 10:40:02 GMT

Satisfying to build using the new interface. Love the cross tab function to only show specific columns, it removes the pain of having to have a select just afterward!

Re: Quest #2: Consumer Purchase Aggregation

FlorianC — Fri, 23 Feb 2024 14:23:55 GMT

Designer Cloud workflow

Re: Quest #2: Consumer Purchase Aggregation

alexnajm — Fri, 23 Feb 2024 18:40:47 GMT

Just a heads up, ">=" is available if you convert your column to a Date!

Re: Quest #2: Consumer Purchase Aggregation

biancang — Fri, 23 Feb 2024 22:11:15 GMT

A familiar looking challenge ... 😁

Re: Quest #2: Consumer Purchase Aggregation

teddim — Fri, 23 Feb 2024 22:29:55 GMT

This is how I solved it. The rounding threw me off a bit, but I figured it out.

Re: Quest #2: Consumer Purchase Aggregation

nwaters — Mon, 26 Feb 2024 13:21:08 GMT

Cloud Quest 2 solution offering

Re: Quest #2: Consumer Purchase Aggregation

RWvanLeeuwen — Mon, 26 Feb 2024 16:05:57 GMT

Not sure why we have this [Other] category all of a sudden... It does not fit the criterium, does it?

Here's my take