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!

[bradshaw]

Nice one.

Spoiler

[geoff_zath]

Spoiler

 

[RWvanLeeuwen]

Thanks @AkimasaKajitani: this helps.

Of note: the formula should be used as REGEX_Match(String, pattern, icase) and does not allow reversal of String and pattern arguments it seems.

[Ladarthure]

Nice one, did not went for transpose then crosstab way though or did I?

I've done a first method which works well but is unable to identify if there are new headers appearing and a second one which is more dynamic