Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)');
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/...
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);
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
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/...