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

[shravanvijayaprasad]

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?

[fmvizcaino · Accepted solution]

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

[BrandonB]

@fmvizcaino I think that a multi row tool could probably take care of this as well doing a group by country

[shravanvijayaprasad]

could you let me know how?

[fmvizcaino]

Hi @BrandonB ,

You would still need to create first a new row for 2017 Canada, but yes, that is another way to tackle the problem! 

Best,

Fernando Vizcaino

[BrandonB · Accepted solution]

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. 

 

 

[shravanvijayaprasad]

This solution is nice too! thanks!