topic Re: Complementing a link table in QlikView

Complementing a link table

Wed, 13 Mar 2013 22:11:06 GMT

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?

Re: Complementing a link table

udit_kumar_sana — Thu, 14 Mar 2013 04:26:06 GMT

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

Re: Complementing a link table

Fri, 15 Mar 2013 00:35:38 GMT

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...

Re: Complementing a link table

udit_kumar_sana — Fri, 15 Mar 2013 03:08:45 GMT

The new_key1 will not work in where exists ,since it's an alias name .The original name key1 should be there.