topic create multiable ranked keys in QlikView

create multiable ranked keys

amien — Wed, 07 Dec 2011 09:14:40 GMT

i have two tables. Each table contains 3 unique key fields to link both tables. Normally i would create a new key based on these 3 values. All 3 keys are unique keys

The only problem is, that for each row, not all 3 fields are filled. it possible that all 3 are filled, but not always.

so, if would have 3 keys:

key1, key2, key3 (most perfect link)

key1, key2 (good link)

key2, key 3 (good link)

key1, key 3 (good link)

key1 (worst link)

key2 (worst link)

key3 (worst link)

the first one should apply first, then the recond one etc. etc.

how can i achieve this in my load script?

in the end i also want to know, per row, on what level both tables are linked

create multiable ranked keys

Thu, 08 Dec 2011 09:41:18 GMT

Hi Amien,

I would do something like this:

if(not isnull(key1) and not isnull(key2) and not isnull(key3),Key1|Key2,Key3,

if(not isnull(key1) and isnull(key2) and isnull(key3),Key2|Key3 etc.......

Succes,

Halmar

create multiable ranked keys

amien — Thu, 08 Dec 2011 10:37:11 GMT

Thanks for your reply

Yes, but its possible that in table 1 i have 2 keys and for table 2 only 1 key .. for the same item.

Those won't be linked with your solution. Because you assume that in both tables the same amount of keys are filled.

example on SAME ITEM and all keys are unique within the same table:

table1

--------

key1 : filled

key2 : filled

key3 : NULL

table 2

----------

key 1: filled

key 2 : NULL

key 3 : NULL