Cloud Quest #10: Reformatting Data

[AYXAcademy]

Hi Maveryx,

We posted the solution JSON file to Cloud Quest 9. 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 10 - Start File.json file into your Analytics Cloud library.
3. All relevant datasets have been preloaded into 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:

For this week's quest, you received product data that was, shall we say... interestingly formatted in Excel with key product information stored at the top of the file. Each data column contains three lines of header information per product (product, market, and type). To make downstream analysis possible, reformat this information to be shown in rows for each product. 

Hint: If you are used to the configuration terminology of the Transpose tool in Designer Desktop:

• "Key Columns" are called "Columns to Group By" in Designer Cloud.
• "Data Columns" are called "Columns to Transpose" in Designer Cloud.

Spoiler

A combination of the Join, Transpose, Cross Tab, 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!

[AkimasaKajitani]

My Solution!

Spoiler

 

[alexnajm]

Good one! Always love messy data...

Spoiler

[Shelbey]

Spoiler

[Qiu]

Fun one!

Spoiler

[RWvanLeeuwen]

Funny thing that I still cannot extract the first three rows using
REGEX_Match('P|M|T', Left([F1], 1), icase=1)

Even though the Designer Cloud automatically shows the icase argument when using auto-complete.

The Designer Cloud Experience states: Parse error at character (37): Unknown column name icase. (Expression #2)

I'll use a workaround in the meantime, but please provide a fix for Designer Cloud so I can actually use string calcs without errors

[RWvanLeeuwen]

I am happy to see the recordID can be applied to the original row order! Awesome!

Here's my solution

Spoiler

[AkimasaKajitani]

@RWvanLeeuwen 

Please use the formula below.

REGEX_Match([F1], "P|M|T", 1)

Basically, do not contain "icase =" in the formula. I think that it is just a guide.  

[mceleavey]

Interesting one. I'm not sure I did this in the the most elegant manner, but hey ho.

Spoiler

I started by splitting out the first three rows using a couple of sample tools, one to skip them one to take them. I then transposed the data grouping by the F1 field and cleansed to remove any leading spaces. I then cross-tabbed back grouped by the Name field and with the F1 as the headings. I then repeated the first step on the rest of the records, simply pivoting into rows then joining the first stream on the name field.
Then I sorted accordingly MARKET ascending and DATE ascending.

10 up! WOOO!

[JeffF]

Spoiler