Group by and return all records in the group as separate columns

[LHouse]

Hello everyone, 

I am doing a transformation in order to group by and return some records as separate columns: 

This yields the following output: 

However, for some Mall_ID-Product_ID pairs, there are more than two FRE's (maximum of 4). I would like to add all FRE's in subsequent columns. This would yield a total of 9 fields: 

Mall_ID	Product_ID	Net_Revs	Deal_Number	Product_ID2	FRE_1	FRE_2	FRE_3	FRE_4
...	...	...	...	...	...	...	...	...
...	..	...	...	...	...	...	...	...

Can anyone help me do that? Your help would be much appreciated! 
Thanks 😊

[atcodedog05]

Hi @LHouse 

This would be job for a tile & crosstab tool. Can you provide sample data as excel.

Rough logic would be set group id using tile/multi-row formula tool to get FRE-1,2,3,4 then crosstab it to table.

[LHouse]

@atcodedog5 Awesome! I just couldn't wrap my head around it. 

Please find sample data attached. Let me know if it is too large. 

[atcodedog05]

Hi @LHouse 

I have worked on a sample of your data. And its turn out there can be upto 6 FRE

1. The top branch is using your logic of summarization

2. In bottom branch i am using multi-row formula tool with groupby on Mall_ID & Product_ID to generate Group row ID

3. In formula tool i am creating the column name "FRE_"+ Group row ID

4. In Cross tab tool i am setting Mall_ID & Product_ID as with new column name as column name field and FRE as value field. Which will turn into table with FRE columns for each FRE values for the pair.

5. Using join tool to combine summarized values and FRE split columns.

Hope this helps 🙂