topic Re: load same distinct key from 2 tables for where exists in QlikView

load same distinct key from 2 tables for where exists

Fri, 11 Jan 2013 15:44:22 GMT

Hello QV Community,

I am checking to see if this code looks correct, I am not getting the expected results. I want to only load ContactKey where it exists in two tables dependent on various criteria. I also want to perform this more than once, but have the second iteration ignore the already loaded data. Is that possible?

//Iteration1

T1:

LOAD DISTINCT ContactKey

FROM fContact.qvd (qvd)

WHERE RecordDate>=$(vMinDate);

INNER JOIN (T1)

LOAD DISTINCT ContactKey

FROM dContact.qvd (qvd)

WHERE Description='Desc1';

Contact:

LOAD ContactKey,

'Category1' AS Category,

ContactName

FROM fContact.qvd (qvd)

WHERE EXISTS (ContactKey);

DROP TABLE T1;

//Iteration2

T3:

LOAD DISTINCT ContactKey

FROM fContact.qvd (qvd)

WHERE RecordDate>=$(vMinDate);

INNER JOIN (T3)

LOAD DISTINCT ContactKey

FROM dContact.qvd (qvd)

WHERE Description='Desc2';

Contact:

LOAD ContactKey,

'Category2' AS Category,

ContactName

FROM fContact.qvd (qvd)

WHERE EXISTS (ContactKey);

DROP TABLE T2;

Re: load same distinct key from 2 tables for where exists

fosuzuki — Fri, 11 Jan 2013 16:06:19 GMT

In your second exists(ContactKey), it will check for values existing in table T3 and the first Contact table. I guess that you want to consider only the values from table T3, right?

Try this:

//Iteration2
T3:
LOAD DISTINCT ContactKey AS ContactKeyT3
FROM fContact.qvd (qvd)
WHERE RecordDate>=$(vMinDate);
INNER JOIN (T3)
LOAD DISTINCT ContactKey AS ContactKeyT3
FROM dContact.qvd (qvd)
WHERE Description='Desc2';
Contact:
LOAD ContactKey,
           'Category2' AS Category,
           ContactName          
FROM fContact.qvd (qvd)
WHERE EXISTS (ContactKeyT3, ContactKey);
DROP TABLE T3;

Re: load same distinct key from 2 tables for where exists

Fri, 11 Jan 2013 16:11:58 GMT

Yes, you are correct.

Re: load same distinct key from 2 tables for where exists

fosuzuki — Fri, 11 Jan 2013 16:17:31 GMT

Did my code work? If so, please mark this thread as answered, as other people may find it useful.

Regards

Fernando

Re: load same distinct key from 2 tables for where exists

Fri, 11 Jan 2013 17:25:14 GMT

Beautiful, thank you!