3 Replies Latest reply: Feb 15, 2018 5:15 PM by Stefan Wühl RSS

    Where not exists isn't working

    Steve Rosebrook
    Steve Rosebrook Feb 15, 2018 4:25 PM

      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
          Stefan Wühl
          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;