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

Transposing The Data

akshayhendre
7 - Meteor

Hello All, I have data in this format 

 

WeekendNew CasesOld CountCompleted Cases
2020-03-294125
2020-04-055237
2020-04-12853
2020-04-190218

 

and would like to have in below format

 

 

Data as of

2020-03-29

Data as of

2020-04-05

Data as of

2020-04-12

Data as of

2020-04-19

Till Date Count

New Case458017
Old Cases12232161
Completed Cases573823

 

So would like to know that what could be the best approach to have the desired output.

10 REPLIES 10
grossal
15 - Aurora
15 - Aurora

Hi @akshayhendre,

 

I think I got you!

 

grossal_0-1587472674771.png

 

Output: 

grossal_1-1587472686072.png

 

What happens:

- I shift all the data

- I add a ID counter for the weeks to keep them in the right order during the Cross Tab

- During the Dynamic Rename I remove the Number and add the 'Data as of' in front

- I also sum up the shifted data with the Summarize Tool and Join them in the end

 

I tried to make it as dynamic as possible. Let me know if it works for you. Workflow is attached.

 

Best

Alex

akshayhendre
7 - Meteor

Thank you Alex for the reply.

As there is no column name provided, I am getting an error 'Error: Formula (13): The field "" is not contained in the record. (Expression #1)' for formula tool.

grossal
15 - Aurora
15 - Aurora

Can you post some sample data? @akshayhendre 

akshayhendre
7 - Meteor

Attached is the sample data @

grossal
15 - Aurora
15 - Aurora

Hi @akshayhendre,

 

your sample data did not indicate that you have the "Till Date Count" in your data, therefore I calculated that value .

 

grossal_0-1587479086921.png

I added a select tool to remove this column in the input to get it working. Or would you like to add this column permanently to your input columns?

 

"Fixed" workflow attached.

 

Best

Alex

 

akshayhendre
7 - Meteor

I am getting an error while extracting the file, can you please the workflow only.

Thank you

grossal
15 - Aurora
15 - Aurora

Workflow only attached.

Ben_H
11 - Bolide

Heres another possible solution

 

Transpose.png

akshayhendre
7 - Meteor

Thank you @grossal for the solution, this is what I was trying to do.

 

Just one thing I would like to ask; what have you done in formula tool to keep the column name dynamic?

Because whenever I try to do the same in different workflow without mentioning the column name, I am getting an error

Error: Formula (3): The field "" is not contained in the record. (Expression #1)

 

Labels