Transposing The Data

[akshayhendre]

Hello All, I have data in this format 

Weekend	New Cases	Old Count	Completed Cases
2020-03-29	4	12	5
2020-04-05	5	23	7
2020-04-12	8	5	3
2020-04-19	0	21	8

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 Case	4	5	8	0	17
Old Cases	12	23	5	21	61
Completed Cases	5	7	3	8	23

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

[grossal]

Hi @akshayhendre,

I think I got you!

Output: 

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]

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]

Can you post some sample data? @akshayhendre 

[akshayhendre]

Attached is the sample data @grossal.

[grossal]

Hi @akshayhendre,

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

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]

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

Thank you

[grossal · Accepted solution]

Workflow only attached.

[Ben_H · Accepted solution]

Heres another possible solution

[akshayhendre]

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)