QVD load with in list check of another QVD

[nate_ak]

I'm rather new to Qlik Sense and am just getting into some slightly advanced QVD loads. I'd like to load all values of a QVD that exist in the result set of another QVD load in my script. I've tried the below use of variables and the EXIST function. It loads successfully, but returns 0 results in my [LP Payoffs] table.

[1st Max Year Month]:
LOAD
    max("Year Month", 1) as Max_Month1
FROM (qvd1);

Let vMaxMonth1 = peek('Max_Month1');

Drop table [1st Max Year Month];

[2nd Max Year Month]:
LOAD
max("Year Month", 2) as Max_Month2
FROM (qvd1);

Let vMaxMonth2 = peek('Max_Month2');

Drop table [2nd Max Year Month];

[Notes Paid This Month]:
Load 
CHR(39) & Concat(DISTINCT "Note Account Number",Chr(39)&','&Chr(39)) &CHR(39) AS Notes_Paid
FROM (qvd2)
Where [Year Month] = $(vMaxMonth1)
and [Note Paid-off This Month Indicator] = 'Y';

Let vNotesPaid = peek('Notes_Paid');

Drop table [Notes Paid This Month];

[LP Payoffs]:
LOAD "Note Account Number",
"Note Bank Share Ledger Balance"
FROM (qvd1)
Where [Year Month] = $(vMaxMonth2)
And Exists([Note Account Number], '$(vNotesPaid)');

[NZFei]

Does everything work if you remove the last line "And Exists([Note Account Number], '$(vNotesPaid)');"?

So change

Where [Year Month] = $(vMaxMonth2)
And Exists([Note Account Number], '$(vNotesPaid)');

to

Where [Year Month] = $(vMaxMonth2);

[Anil_Babu_Samineni]

Exists function works only for fields not for filed rows. So, you need to define the field one instead variable which holding the value.

[rwunderlich]

I believe you are misunderstanding the Exists parameters. The two parameters are fieldnames, which indirectly represent a set of values. The parameters are not the values themselves. 

Exists(field1, field2)

"Test the value contained in field2 for the record I am currently loading against all previously loaded values of field1.  If there is a match, return true,  else false".

So for your example you should be able to write it as:

Exists([Note Account Number], [Note Account Number])

or the simplified version that assumes the first param is the same as the first:

Exists([Note Account Number])

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[nate_ak]

Yes the script works and returns results if the Exists function line is removed. The Exists line is my attempt to filter the results down to the values listed in the [Notes Paid this Month] table.

Is there an alternative method to accomplish what I'm trying to do here? I want to filter the results of the [LP Payoffs] table to include only the values that exist in the [Notes Paid This Month] table.

[nate_ak · Accepted solution]

I found the solution in another thread here: https://community.qlik.com/t5/QlikView-Scripting/Loading-data-if-it-does-not-exist-in-another-table/...