unsure of how to join multiple data sets

accounting1234

I am trying to figure out the best way to join multiple data sets. Each set is static in that it is populated at a specific date. For example my three data sets could be:

Table 1: as at Month 1

CustIDSale

1	$100
2	$50
3	$50
4	$75

Table 1: as at Month 2

CustIDSale

1	$90
2	$30
5	$200
6	$250

Table 1: as at Month 3

CustIDSale

1	$75
5	$150
6	$215
7	$150

Ending table I would like to have after joining the three tables above

CustIDSale Month 1Sales Month 2Sale month 3

1	100	75	75
2	50	30	null
3	50	null	null
4	75	null	null
5	null	200	150
6	null	250	215
7	null	null	150

I've simplified the situation a fair bit, I understand it may be a bit of an unusual thing to have rolling data. I am unsure if this would be a join or a union, some help would be greatly appreciated. Some background of the data, i is all as at basis, so 1 month's data could have more or less than the previous month's. In total, i have 7 tables or 7 months of data i need to "join." Any help would be greatly appreciated!

jasperlch

Hi @accounting1234 ,

This can be achieved by first using a Union tool, then a Cross Tab tool:

Please note that a "month" field is needed for each stream (or data source). In the example, the month field is added by Formula tool.

Thanks,

Jasper

New Workflow1.yxmd