topic Where not exists isn't working in App Development

Where not exists isn't working

steverosebrook — Thu, 15 Feb 2018 21:25:25 GMT

I have two tables containing asset data: one is a "Current" table, containing month-end values by client for the past 13 months; the other is a "History" table, containing year-end values by client from 2009-2017. I want to concatenate these two tables together, but there's one obvious problem: there are duplicative dates in each table. Right now in the "Current" table, there's data for 12/31/2017 (because that's one of the months in the past 13 months), but there's also data for 12/31/2017 in the "History" table - since that's a year-end. I only want to load the 12/31/2017 data once - from the Current table. And then exclude it from being loaded from the History table.

I've tried loading the history table using a "where not exists" clause, but I can't seem to get it to work. I either get no data at all from the history table, or I get just a small handful of client accounts coming in from the history table. One of the issues is that when I load the current table, I alias the date field. So in the where not exists clause I'm not sure whether to use the alias name or the source field name. I've tried both, and I've tried the two-parameter version as well - nothing works.

Here's my code:

Assets:

LOAD

AGREEMENT_ID as "Agreement ID",

date(ACCOUNTING_DATE) as "Assets As Of",

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("Assets As Of",ACCOUNTING_DATE)

;

Any help would be appreciated.

Thanks!
Steve

Re: Where not exists isn't working

swuehl — Thu, 15 Feb 2018 21:38:59 GMT

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

steverosebrook — Thu, 15 Feb 2018 22:11:48 GMT

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

swuehl — Thu, 15 Feb 2018 22:15:01 GMT

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.