We are in the final days to enter our Data and Analytics - "What's your why?" raffle with SparkED!

Reply with your "why" in text/video format to our forum post by January 28 to be entered! 

Alteryx Designer Discussions

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

Cross Tab after Transpose

BonusCup
8 - Asteroid

I have some data where I Transpose the data to change some of the field names from an integer to a date using Find/Replace.  I can't seem to figure out how to get the data back to the desired view like below. 

 

Desired:

RecordIDcolumn_1column_2column_3column_48/11/20218/12/20218/13/20218/14/20218/15/20218/16/20218/17/20217Day_total7Day_avg8/18/2021% Diffalert
1Data1  owner10000000002200alert
2Data2  owner134433001720200alert

 

Source:

NameValue
RecordID1
column_1Data1
column_2 
column_3 
column_4owner1
8/11/20210
8/12/20210
8/13/20210
8/14/20210
8/15/20210
8/16/20210
8/17/20210
7Day_total0
7Day_avg0
8/18/20212
% Diff200
alertalert
RecordID2
column_1Data2
column_2 
column_3 
column_4owner1
8/11/20213
8/12/20214
8/13/20214
8/14/20213
8/15/20213
8/16/20210
8/17/20210
7Day_total17
7Day_avg2
8/18/20210
% Diff200
alertalert

 

BonusCup_0-1629379065630.png

 

3 REPLIES 3
atcodedog05
21 - Polaris

Hi @BonusCup 

 

Here is how you can do it.

Workflow:

atcodedog05_0-1629379499996.png

 

1. Using first mult-row formula to calculate row id. 

2. Using first mult-row formula to calculate col id. This will be used for column rename.

3. Using to summarize groupby on col id to get list of column names.

4. Using crosstab to convert to table with col name as col id.

5. Using dynamic rename to rename col id with col name.

 

Hope this helps : )

 

BonusCup
8 - Asteroid

@atcodedog05 thanks for such a quick response and the solution to my issue.  Reviewing your sample WF I can understand how you were able to solve it. 

 

Thanks again

atcodedog05
21 - Polaris

Happy to help : ) @BonusCup 

Cheers and have a nice day!

Labels