Transpose tool

[aparna0208]

Hi,

I tried using data cleansing and transpose tool but not getting what I expected. Not sure how to get rid or blanks/nulls and have the date on top as indicated in second example. Need some help. Thank you in advance!

Output

Date	Fails	Count
11/6/2020
	SF CRD not populated	880
	Discovery Data CRD not populated	240
	SEC# not populated	22919
	SV CRD not populated	2

Desired output

Fails	11/6/2020
SF CRD not populated	880
Discovery Data CRD not populated	240
SEC# not populated	22919
SV CRD not populated	2

[atcodedog05]

Hi @aparna0208 

Can we see your input data.

[echuong1 · Accepted solution]

You can use the cross-tab to achieve this. First you'd want to "drag down" the date so it applies to every row. This can be done with the multi-row formula.

I added in a dynamic rename to clean up the field name. Special characters are automatically changed to "_" with the cross-tab.

[messi007]

Dear @aparna0208,

Suppose your input look like :

I have done Summarize in order to count then I have done the cross tab

Attached the workflow hope that helps

Regards,

[aparna0208]

@messi007 thanks for the quick response:) My input is bit different so had to add in tools as per other solutions

[aparna0208]

@echuong1 thanks for the quick response:) It worked as expected. Thank you for your inputs:)

One quick question about adding one more feature. Now the next time when I run the workflow, I want only the date column appended to the right of the existing column. Something like below

Fails	11/06/2020	11/6/2020
Discovery Data CRD not populated	240	240
SEC# not populated	22919	22919
SF CRD not populated	880	880
SV CRD not populated	2	2

But when I append the output to same file and run the workflow it doesn't seem to work. It gets appended at the bottom as seen below

Fails	11/06/2020
Discovery Data CRD not populated	240
SEC# not populated	22919
SF CRD not populated	880
SV CRD not populated	2
	240
	22919
	880
	2

Is there a way to append the output during my next run as indicated in first example?

Thanks!