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!
Solved! Go to 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
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.