Help required on transposing data

[skychar]

Hi, I have client which purchased multiple products. I would like to list the product IDs in row. 

I have tried unique to remove duplicates, but those with different product ID is being removed as well. I tried transpose, but i could not achieve the output i needed.

I have attached a file. Could anyone please help?

[Kenda · Accepted solution]

Hey @skychar 

You were close, but it is actually the Cross Tab tool that will be your key here!

First, however, you need to create a new field that will be used as your headers. This can easily be done with the Multi-Row Formula tool. Simply group by Client ID and use this expression: 

[Row-1:New Field]+1

Then, add your Cross Tab tool, grouping by Client ID, using New Field as your headers, and Product ID as the values for new columns. You can select anything for the method of aggregation, because we took care of this uniqueness when creating our new field. 

Hope this helps!

[fmvizcaino · Accepted solution]

Hi @skychar ,

Attached is a solution showing how to do it.

Take a look and let me know if you need further assistance.

Best,

Fernando Vizcaino

[skychar]

Hi Kenda, 

It works! Actually, I have tried both multi-row formula & cross tab earlier. However, I have chosen the wrong item for change column header and value for new header, thus it end up with a different thing. 

Thanks!

[skychar]

Hi Fernando Vizcaino,

It works. I have just add 'Select' tool to change the 'Type' of input to remove the "[null]".

I have learnt new things from your solution, thanks!