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 · Accepted solution]

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

[SteveWayne]

Hey Accounting1234

You can acheive this by

1. joining all files together

2. use the formula tool to create a single Cust Id column

3. Select just the required cols

Example attached

[Avinash_K]

You could use the Join Multiple tool , multirow formula and select tool to easily achieve this 

[cmcclellan]

7 months, all the same format ..... does that mean more months are coming as well ?

I'd use a wildcard input on your Input Data tool and enable "Output File Name as Field", then use that to create new fields (Formula tool) and then Summarize to get back to your output.

The most important question though - is there only 7 months, or are you adding a new file every month (so in 3 months it will be 10 files) - you want something dynamic enough so you don't have to change the workflow every month.