load same distinct key from 2 tables for where exists

[#1]

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;

[fosuzuki · Accepted solution]

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;

[#3]

Yes, you are correct.

[fosuzuki]

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

Regards

Fernando

[#5]

Beautiful, thank you!