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

Question

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_IDProduct_IDNet_RevsDeal_NumberProduct_ID2FRE_1FRE_2FRE_3FRE_4.....................................................

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

atcodedog05 · Answer

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 🙂

split into cols.yxmd

LHouse · Answer

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

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

LHouse sample data.xlsx

atcodedog05 · Answer

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.