topic Re: Including items from a composite key in QlikView

Including items from a composite key

Tue, 30 Jul 2013 08:00:15 GMT

Hi I am loading two sources from my data like this:

MAINLIST:

LOAD DISTINCT

TEXT(PROD_ID) & TEXT(GEN_ID) & TEXT(SIZE) AS MAIN_IDENT,

PROD_NAME as MAIN_NAME

FROM

[data2.csv]

where MAIN_IND = 1

(txt, utf8, embedded labels, delimiter is ',', msq)

OTHERLIST:

LOAD DISTINCT

TEXT(PROD_ID) & TEXT(GEN_ID) & TEXT(SIZE) AS OTHER_IDENT,

PROD_NAME as OTHER_NAME

FROM

[data2.csv]

where MAIN_IND = 0

(txt, utf8, embedded labels, delimiter is ',', msq)

I need to load OTHERLIST to get only the items where OTHER_IDENT value is found in the MAINLIST's MAIN_IDENT

Example

MAINLIST:

MAIN_NAME, MAIN_IDENT

MY PRODUCT, 10001

MY PROD 2, 10002

OTHER LIST

OTHER PROD 1, 10001

OTHER PROD 2, 10002

OTHER PROD 3, 10003 <-- want to exclude this value

Re: Including items from a composite key

VishalWaghole — Tue, 30 Jul 2013 08:08:10 GMT

Left join OTHERLIST table into MAINLIST by creating link key as IDENT Like this :

MAINLIST:

LOAD DISTINCT

TEXT(PROD_ID) & TEXT(GEN_ID) & TEXT(SIZE) AS IDENT,

PROD_NAME as MAIN_NAME

FROM

[data2.csv]

where MAIN_IND = 1

(txt, utf8, embedded labels, delimiter is ',', msq);

left join (MAINLIST)

LOAD DISTINCT

TEXT(PROD_ID) & TEXT(GEN_ID) & TEXT(SIZE) AS IDENT,

PROD_NAME as OTHER_NAME

FROM

[data2.csv]

where MAIN_IND = 0

(txt, utf8, embedded labels, delimiter is ',', msq);

Thanks

Re: Including items from a composite key

teempi — Tue, 30 Jul 2013 08:13:19 GMT

Hey,

If you want to keep the tables separated on purpose, you can use something like this:

OTHERLIST:

LOAD DISTINCT

TEXT(PROD_ID) & TEXT(GEN_ID) & TEXT(SIZE) AS OTHER_IDENT,

PROD_NAME as OTHER_NAME

FROM

[data2.csv]

where MAIN_IND = 0 AND Exists(MAIN_IDENT, TEXT(PROD_ID) & TEXT(GEN_ID) & TEXT(SIZE))

(txt, utf8, embedded labels, delimiter is ',', msq)

-Teemu

Re: Including items from a composite key

Tue, 30 Jul 2013 08:15:26 GMT

Thanks, let me try this.