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 DateFailsCount11/6/2020 SF CRD not populated880 Discovery Data CRD not populated240 SEC# not populated22919 SV CRD not populated2 Desired output Fails11/6/2020SF CRD not populated880Discovery Data CRD not populated240SEC# not populated22919SV CRD not populated2

Transpose tool

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

Output

Accepted answers

echuong1

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.

New Workflow5.yxmd

All comments

atcodedog05

Hi @aparna0208

Can we see your input data.

echuong1

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.

New Workflow5.yxmd

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,

CrossTab.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703