-
Re: Where not exists isn't working
Stefan Wühl Feb 15, 2018 4:39 PM (in response to Steve Rosebrook)Remember that Exists() is checking against the field's symbol table (field is the first argument to the function) and that the symbol table is update with every record loaded into a resident table.
Try it like
Assets:
LOAD
AGREEMENT_ID as "Agreement ID",
date(ACCOUNTING_DATE) as "Assets As Of",
ACCOUNTING_DATE as Date_Check,
ASSETS as "Assets"
FROM [lib://$(vDataConnectionString)/TBL_RR_FUND_ASSETS.qvd] (qvd)
;
//Pull in asset data for prior years.
Concatenate(Assets)
LOAD
AGREEMENT_ID as "Agreement ID",
date(ACCOUNTING_DATE) as "Assets As Of",
ASSETS as "Assets"
FROM [lib://$(vDataConnectionString)/TBL_RR_FUND_ASSETS_HISTORY.qvd] (qvd)
where not exists(Date_Check,ACCOUNTING_DATE)
;
DROP FIELD Date_Check;
-
Re: Where not exists isn't working
Steve Rosebrook Feb 15, 2018 5:11 PM (in response to Stefan Wühl )Thanks Stefan - that worked. I appreciate the quick reply!
If I understand it correctly, the real issue was that I was formatting the "Assets As Of" field as a date, and that was causing the exists comparison with the unformatted Accounting_Date field to fail. Creating a second unformatted date field did the trick.
Thanks again,
Steve
-
Re: Where not exists isn't working
Stefan Wühl Feb 15, 2018 5:15 PM (in response to Steve Rosebrook)No, it was probably not the formatting that caused your issue (at least that is not what I tried to explain).
Take a look at
Symbol Tables and Bit-Stuffed Pointers
and then re-read my previous answer, with attention to the very first sentence.
-
-