Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I need your help with following problem:
I have to join 4 tables with 2 keys (ID and Date). Every table has a different set of data (with respect to ID) so I need an outer join as I dont want to loose data. My goal is to have one single table with a distinct ID for every date.
Example
ID-Number | Date | dimension xy | value1 | value2 | value3 | value4 |
1234 | 2020/12/31 | aa | 1111 | 2222 | 3333 | 4444 |
1234 | 2021/12/31 | aa | 1112 | 2223 | 3334 | 4445 |
1234 | 2022/12/31 | aa | 1113 | 2224 | 3335 | 4446 |
My problem now is that the outer join generates duplicate records in the final table (except for the first date), with the effect that I cant filter my data anymore (e.g. by dimension xy).
ID-Number | Date | dimension xy | value1 | value2 | value3 | value4 |
1234 | 2020/12/31 | aa | 1111 | 2222 | 3333 | 4444 |
1234 | 2021/12/31 | aa | 1112 | 0 | 0 | 0 |
1234 | 2021/12/31 | - | 0 | 2223 | 3334 | 4445 |
1234 | 2022/12/31 | aa | 1113 | 0 | 0 | 0 |
1234 | 2022/12/31 | - | 0 | 2224 | 3335 |
4446 |
All dimensions beside ID & Date are in the main table. How can I aggregate all information into one unique pair of ID & Date?
Does anyone have a solution for this? I´ve already tried a lot of things and nothing seems to help.
Best wishes
Matthias
I am having a similar problem. Does anyone have a solution for this?
Instead of joins could be mappings used which have no risks to change the number of records and are usually more performant.
Beside this it's often better not to merge the data horizontally else vertically which means just to concatenate (union in sql) the data.
- Marcus