data structure required

[Sharad_123]

I attach excel file that contains 2 sheets 

1 sheet is data structure available 

2 sheet is data structure required 

so I want to solution to how can I do 1st sheet to 2nd conversion into alteryx workflow  .. thank you 

sheet 1  -  data structure available 

Customer No	Name	Order No	Order Date	Shipment Date	Item No	Quantity	Unit	Net Amount	Amount	Currency
1	abc
		O_1	01/09/2021	03/09/2021
				03/09/2021	I_1	2	pcs	100	200
				03/09/2021	I_2	2	pcs	50	100
Total						4			300
2	def
		O_2	01/09/2021	03/09/2021
				03/09/2021	I_3	5	pcs	200	1000	USD
				03/09/2021	I_4	4	pcs	50	200	USD
		O_3	01/10/2021	03/10/2021
				03/10/2021	I_5	2	pcs	100	200	USD
Total						11			1400	USD

sheet 2 - data structure required 

Customer No	Name	Order No	Order Date	ShipmentDate	Item No	Quantity	Unit	Net Amount	Amount	Currency
1	abc	O_1	01/09/2021	03/09/2021	I_1	2	pcs	100	200
1	abc	O_1	01/09/2021	03/09/2021	I_2	2	pcs	50	100
2	def	O_2	01/09/2021	03/09/2021	I_3	5	pcs	200	1000	USD
2	def	O_2	01/09/2021	03/09/2021	I_4	4	pcs	50	200	USD
2	def	O_3	01/10/2021	03/10/2021	I_5	2	pcs	100	200	USD

[Maskell_Rascal]

Hi @Sharad_123 

Here are two different ways you can do it. 

Solution 1 uses a lot of Multi-Row Formula tools to fill down your data before filtering to the final results. While this solution will work, its a little messy and not very dynamic should you need to add additional fields. 

Solution 2 first transposes only the fields that need to be filled down. It then will sort the data, use a Multi-Row Formula tool grouped by the column names to fill down all fields at the same time, and the crosstab the data back. From there it joins back to the original data by RecordID and filters to the final results. 

If this solves the problem please mark answer as correct, if not let me know!

Cheers!

Phil

[Sharad_123 · Accepted solution]

Hi 

in both workflows you did text input tool  ..that time workflow going well .. but the same when I do input data tool using my attached excel file that time getting error. if possible look ones again..thank you.

 and in 2nd solution workflow going well but when browsing data that time name column showing empty ..using input data tool.. so please guide for using input data tool for this .. thank you once again.

[Maskell_Rascal · Accepted solution]

Hi @Sharad_123 

Both solutions are failing b/c I renamed the field [Name ] to [Name]. You for some reason have a trailing whitespace in your field name.

You can update solution 1 by correcting the formula and adding the space.

For solution 2, click on the Transpose tool and select the field that is missing from the Data Columns selection. 

Thanks!

Phil

[Sharad_123]

thank you so much for guidance..