Display data from multiple rows to one row

[Chirag_Gandhi07]

Hi everyone,

I have attached an example where there are two separate records for user with email test1@test.com. I want to only have that user appear once in my output. How do I make it so that test1@test.com displays SW 1 and SW 3 on the same row? Any suggestions would be much appreciated.

Thank you

[Kenda]

Hey @Chirag_Gandhi07!

I would recommend first using the Transpose tool, using Email as a key field. Next, Filter out the empty Values. Lastly, use a Cross Tab tool, grouping by the Email field. This should do the trick!

[ponraj]

Here is the sample workflow for your case.  Hope this is helpful. 

WorkflowResults

[Chirag_Gandhi07]

Thank you. I noticed that in the output, field 4 moves ahead of field 3. Is it possible to have them in order but still on the same row? So field 2, field 3, and then field 4

[Kenda]

@Chirag_Gandhi07 One way to do this would be to take only the first row from your original data then Union it to your output from the Cross Tab tool. In the Union tool, ensure that you choose to set a Specific Output Order and make your one sample row the first row. Finally, just use another Sample tool to get rid of that first extra row. The reason this works is because the Union tool will preserve the field order for the data you set to be the first in the output order. See the attached workflow. Hope this helps!

[ponraj]

Here is another sample workflow. Hope this is helpful. 

WorkflowResults

[jwalder]

I think there is risk in the assumption that you will have only version of an email populated for any given column, but regardless, the solution is quite simple. Just use the Summary Tool, grouping by the Email and concatenating the columns. This solution means you don't lose any data that mayhaps be on other versions of a given email. It retains the column order, and it is a single tool instead of a bunch of them.

[Chirag_Gandhi07]

Hi @Kenda I have attached a sample workflow below that shows how my workflow is set up. This sample only contains 7 records but my real workflow contains a lot more. I tried your suggestion to get them on the same row and to have only one email populate for each user but I am unable to do so. If you could help out, I would really appreciate it.

[Chirag_Gandhi07]

Hi @jwalder That is a good suggestion but I have 3667 columns

[Kenda]

@Chirag_Gandhi07 Can you attach your example xlsx file separately, please? The input file does not carry with the workflow unless you export it.