Hi all, I have a simple example input YearCountryAmount2015US902015Canada952016US1002016Canada1052017US110 I want to get the prior year amount as another column, when I do a join I can get YearCountryAmountPY Amount2015US9002015Canada9502016US100902016Canada105952017US110100 But I want to get YearCountryAmountPY Amount2015US9002015Canada9502016US100902016Canada105952017US1101002017Canada0105 The last line 2017 - Canada isn't showing up if I do a join. Can someone help me with this? Example.xlsx

Join with same dataset but get details from the right as well

Hi all, I have a simple example input

Year	Country	Amount
2015	US	90
2015	Canada	95
2016	US	100
2016	Canada	105
2017	US	110

I want to get the prior year amount as another column, when I do a join I can get

Year	Country	Amount	PY Amount
2015	US	90	0
2015	Canada	95	0
2016	US	100	90
2016	Canada	105	95
2017	US	110	100

But I want to get

Year	Country	Amount	PY Amount
2015	US	90	0
2015	Canada	95	0
2016	US	100	90
2016	Canada	105	95
2017	US	110	100
2017	Canada	0	105

The last line 2017 - Canada isn't showing up if I do a join. Can someone help me with this?

Example.xlsx

Join

Accepted answers

fmvizcaino

Hi @shravanvijayaprasad ,

Attached is an example showing how to do it. I needed to add a few more tools to automatically remove future years (in your dataset it would be 2018)

LEt me know if this works for you.

Best,

Fernando Vizcaino

2020-02-10 Join with same dataset but get details from the right as well.yxmd

BrandonB

Similar concept but slightly different approach. If every row existed including 2017 Canada then it would be as easy as just using a multi row tool. Because this is not the case, I created every combination of year/country, found which ones did not exist in the data set because they fell out of the right side of the join. Then assigned a zero placeholder for the amount. Finally, once every year and country combo was in the data set, we can then use the multi row tool to pull the value from the previous row within the country group and finally sort.