Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Flatten Data which have different number of columns

Andyhjtran
6 - Meteoroid

Hi All,

 

I'm trying to flatten my data into only 3 columns, (1 for the line item, 1 for the column name, and 1 for the value). The issue for me right now is that line items that start with 1, have 2 columns. Then line items that start with a 2 have 4 columns and so on. Thus, it makes it difficult to transpose given that each line item has a different amount of columns.

 

So this is the data originally:

Andyhjtran_0-1632788246288.png

 

This is the objective (after being flattened)

Andyhjtran_1-1632788266457.png

 

I have created an Alteryx workflow to solve this (I have attached this below). 

Andyhjtran_2-1632788288060.png

 

Just to explain what I've done:

1) Multi-row Tool: To allocate a Record ID to each line item

2) Filter tool: Filter for each Record ID (can group them if they have the same column headers and the same amount of them)

3) Select tool: Remove any columns with null values

4) Dynamic rename tool: Take the first row as headers

5) Filter tool: Remove the rows where there aren't any values we want to transpose

6) Transpose tool: Make the data flat

7) Union tool: Union all the data together

 

However, is there anyway to do this without allocating a Record ID and filtering it based on the amount of columns the line item has? I have to do a similar exercise for 40 different data inputs, so it'll take forever for me to configure to record ID tool and the select tool to match each data set.

 

Thank you for your help

 

 

5 REPLIES 5
apathetichell
18 - Pollux

See attached for an idea of how to do this dynamically... It involves a batch macro.

Andyhjtran
6 - Meteoroid

Hi apathetichell,

 

Thank you for taking time out to help me with my issue. Unfortunately, I'm running an older version of Alteryx and wasn't able to open your Workflow. Can you please tell me how you configured the batch macro tool?

 

Really appreciate it!

 

Regards,

Andy

Qiu
20 - Arcturus
20 - Arcturus

@Andyhjtran 
Check this one for the downgrade.
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-downgrade-packaged-workflow-Fil...

@atcodedog05 
Where is your latest post on downgrading workflows, I can not find anywhere. 😁

Qiu
20 - Arcturus
20 - Arcturus

@atcodedog05 
Thanks.
I though I put it in the bookmark. 😁

Labels