Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Data Transformation

PN2527
8 - Asteroid

Screenshot_2021-04-23-22-55-25-800_com.microsoft.office.excel.jpg

I need the output as shown in the above image. I have attached the data for reference.  

 

Thanks for the help.

3 REPLIES 3
AngelosPachis
16 - Nebula

Hi @PN2527 ,

 

You can use a multi-row formula tool to create groups of entity/units based on a parent entity ID since those appear in sequence (So entity id | unit | entity id | unit | unit | entity id ...). Then you can use a filter tool to split between entities and units and join on the parent entity ID

 

AngelosPachis_0-1619201105697.png

 

apathetichell
19 - Altair

This works with your sample data. A few things - it uses a multi-row to separate out units and entities.  Then it filters out the stuff you don't need. It crosstabs - but then does a text to column (to create rows) as some entities have multiple units.

mattnason1
9 - Comet

I was curious if I could do this without a multi-row tool since that's what was made for this type of situation.  This would not work with large data sets because the append field piece would blow up the process.  But it was fun to figure out.

mattnason1_0-1619206235926.png

 

Labels