topic How do I group data together from different rows based on a common field? in Alteryx Designer Desktop Discussions

How do I group data together from different rows based on a common field?

CherieACI — Mon, 23 Aug 2021 17:55:06 GMT

Hi,

In my dataset I list invoice numbers which could have multiple lines per invoice with different information on each line. What I need to do is turn the multiple lines into one line and combine the Rev GL Acct numbers and Revenue Start Dates together.

For example, the first 4 columns are from the data and the last 2 columns are what I need:

Sales Order Num	Invoice Num	Rev GL Acct	Revenue Start Date	Need: All Rev GL Accts Listed	Need: All Revenue Start Dates Listed
Sales Order Number 1	1310000085	4010	4/1/2020	4010, 4400, 4400	4/1/2020, 10/1/2019, 4/1/2020
Sales Order Number 1	1310000085	4400	10/1/2019	eliminate this line
Sales Order Number 1	1310000085	4400	4/1/2020	eliminate this line

How do I do this?

Thank you!

Cherie

Re: How do I group data together from different rows based on a common field?

atcodedog05 — Mon, 23 Aug 2021 17:59:31 GMT

Hi @CherieACI

You can use summarize tool to groupby and concat values. Refer the below config.

Workflow:

Hope this helps : )

Re: How do I group data together from different rows based on a common field?

JosephSerpis — Mon, 23 Aug 2021 18:00:55 GMT

Hi @CherieACI you can do that using the summaries tool and concatenate string fields any numeric fields will need to be changed beforehand. You will also need to group by certain columns such as invoice numbers.

Re: How do I group data together from different rows based on a common field?

CherieACI — Mon, 23 Aug 2021 19:55:08 GMT

Thank you that worked!

Re: How do I group data together from different rows based on a common field?

atcodedog05 — Tue, 24 Aug 2021 05:18:02 GMT

Happy to help : ) @CherieACI

Cheers and have a nice day!