Merging lines into one

[AJ-Fahmy]

Hello Experts,

I have this requirement that I'm kinda struggling in solving it. the business is looking to view its clients based on the business name, Invoice number, current year, current year Order #, Current year Amount, and Last year amount under the same line.

This is how the data is showing right now

Business Name	Invoice#	Order#	Year of CloseDate	Amount
ABC WH	1432540	1111553	8/1/2020	15052
ABC WH	1432540	970806	8/20/2019	16724
HD	63221	211133	3/14/2020	73009
HD	63221	455443	3/21/2019	73009
Art Van	82003	101223	6/10/2020	3455
Art Van	82003	8334300	5/30/2019	7433
BBY	91922	48449	8/10/2020	25800
BBY	91922	1121	8/10/2019	24750

And this is how the business wants to show the data

Business Name	Invoice#	Order#	Year of CloseDate	Amount	last year Amount
ABC WH	1432540	1111553	8/1/2020	15052	16724
HD	63221	211133	3/14/2020	73009	73009
Art Van	82003	101223	6/10/2020	3455	7433
BBY	91922	48449	8/10/2020	25800	24750

Important note that if I filter the results by the date I will get this year and last year Amounts. So if I say I will filter the results by this quarter, I should get

Business Name	Invoice#	Order#	Year of CloseDate	Amount	last year Amount
ABC WH	1432540	1111553	8/1/2020	15052	16724
BBY	91922	48449	8/10/2020	25800	24750

Thank you so much

[vizAlter · Accepted solution]

Hi @AJ-Fahmy — Try this solution...

After adding "Select" tool:

[ArtApa · Accepted solution]

Hi @AJ-Fahmy - Almost everything in Alteryx can be done in a couple of different ways:

[ImadZidan · Accepted solution]

Hello @AJ-Fahmy ,

Besides the great solutions, her is another one.

Hope you find it insightful.

[grazitti_sapna · Accepted solution]

Hi @AJ-Fahmy , give this a try.