Transformations using summary/crosstab

[harshsaraf]

Hi All,

Need some hep with transformation tools summarize and cross tabs. I am just struggling to use them in the correct order to change the below input to output table. Any help/ if you could share the workflow - will be very helpful and thankful to the community.

Please refer to Excel attached for input and output table.

Merry Christmas 🙂

Input:

Billing Doc Type Description	Material Group 1 Description	Month	Sold-to party Description	Count	MedianNo0_% Discount	ModeNo0_% Discount	AvgNo0_% Discount
ESP Project Invoice	ACBs C power (ESP)	1	A. R. ELECTRO POWER PVT. LTD.	1	-0.53	-0.53	-0.53
ESP Standard Invoice	ACBs C power (ESP)	2	A. R. ELECTRO POWER PVT. LTD.	3	-0.58	-0.58	-0.58
ESP Project Invoice	ACBs C power (ESP)	2	ACME ELECTRICAL & INDUSTRIAL CO	5	-0.625	-0.625	-0.59
ESP Standard Invoice	ACBs C power (ESP)	2	ACME ELECTRICAL & INDUSTRIAL CO	8	-0.58	-0.58	-0.58

Output Table

Material Group 1 Description	Sold-to party Description	ESP Project Invoice				ESP Project Invoice				ESP Standard Invoice
Month 1				Month 2				Month 2
Count	MedianNo0_% Discount	ModeNo0_% Discount	AvgNo0_% Discount	Count	MedianNo0_% Discount	ModeNo0_% Discount	AvgNo0_% Discount	Count	MedianNo0_% Discount	ModeNo0_% Discount	AvgNo0_% Discount
ACBs C power (ESP)	A. R. ELECTRO POWER PVT. LTD.	1	-0.53	-0.53	-0.53					3	-0.58	-0.58	-0.58
ACBs C power (ESP)	ACME ELECTRICAL & INDUSTRIAL CO					5	-0.625	-0.625	-0.59	8	-0.58	-0.58	-0.58

[TrevorS]

Hello @harshsaraf 
Can you please upload a copy of your workflow with what you have attempted so far so that the Community can better troubleshoot the issue for you?
Additionally, I would recommend checking out some of the interactive lessons regarding the use of Summary and Crosstab so that you can attempt this yourself!

Please check out the resources below to get started!
Summarizing Data Interactive Lesson

Changing Data Layouts 

Additionally, the examples provided within the Designer tool also can assist you with live samples of how these tools can work.

If you are still having trouble, please let the community know where the issue lies so that they can best assist you further!
Thanks,
TrevorS

[marcusblackhill]

Hey @harshsaraf !

That tools is used to be confusing much times even to experienced professionals. I developed with @carlosteixeira another tool to help in this cases, being similar to pivot table of excel.

To your case I needed to do a little change in that tool, but the original is here in that link: Pivot Table Tool 

Attached is a package with you data source and my customized tool to you run.

Hope that help!