Cloud Quests

Elevate your workflow skills by solving real-world challenges using the Alteryx Analytics Cloud Platform.

Cloud Quest #10: Reformatting Data

AYXAcademy
Alteryx
Alteryx

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:

 

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!

AYX-Maveryx_Micro Identity-Maveryx Academy_wordmark.png

18 REPLIES 18
AkimasaKajitani
17 - Castor
17 - Castor

My Solution!

 

Spoiler
スクリーンショット 2024-06-13 010336.png

alexnajm
17 - Castor
17 - Castor

Good one! Always love messy data...

Spoiler
Quest #10 Snap.png
Shelbey
Alteryx
Alteryx
Spoiler
Screenshot 2024-06-12 152956.png
Qiu
21 - Polaris
21 - Polaris

Fun one!

Spoiler
Cloud Quest 10 - Start  File-Qiu.png

 

RWvanLeeuwen
11 - Bolide

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
11 - Bolide

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

 

Here's my solution

Spoiler
Quest 10 - RWvL.png
AkimasaKajitani
17 - Castor
17 - Castor

@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
17 - Castor
17 - Castor

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.

Workflow.pngResults.png

10 up! WOOO!



Bulien

JeffF
Alteryx
Alteryx
Spoiler
Quest10_JeffF.png