Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a link table where I match accounts from one place, to the matching account from the other. However, this list is not complete so I need to complete the link table from both sides in order to allow the whole numbers are reachable.
LinkTable:
Load CalendarId,
Key1,
Key2
Resident LinkTable;
Then I want to parse both fact tables in order to find the missing records and add them, without duplicating the already found members. I tried contatenating
the results, but duplicated records were created for the records existent on the original link table.
LinkTable:
Load CalendarId,
Key1,
Key2
Resident LinkTable;
Concatenate
Load Distinct
CalendarId,
Key1
Resident FactTable1;
I cannot figure out a way to make an outer join to find the missing records and just concatenate them because I cannot explicitly specify the join condition.
Any suggestions?
Hi,
You can try like this:
LinkTable:
Load CalendarId,
Key1,
Key2
Resident LinkTable;
Concatenate
Load Distinct
CalendarId,
Key1 as new_key1
Resident FactTable1;
where not exists(key1,key1);
Concatenate
Load Distinct
CalendarId,
Key2 as new_key2
Resident FactTable2;
where not exists(key2,key2);
new_Key1,new_key_2 is field of record which doesnot exists in linktable.
Regards,
Udit
Thanks Udit,
It appears it's a change to "where not exists(key1,key1);" to "where not exists(key1,new_key1);" ?
However, I've been unable to verify it. It appears as the fact table is already loaded, the where clause actually won't reflect any records
I'l try to twist the logic on the Link table use separate names and finally rename the fields as a last step.
Here we go...
The new_key1 will not work in where exists ,since it's an alias name .The original name key1 should be there.