Including items from a composite key

[#1]

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

[VishalWaghole]

Hi

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

[teempi · Accepted solution]

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

[#4]

Thanks, let me try this.