Outer join several tabels over 2 keys

[mweiherer]

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

[StefanW]

I am having a similar problem. Does anyone have a solution for this?

[marcus_sommer]

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