Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

"Where not exists" not working

Hi,

I'll try to build a table that shall be updated each month. To be sure, that not the data for the same month is added again, I'll try to use the "where not exists clause".

Target table shall be:

TERRITORY_ID     Month     Value

a                         201401     50

a                         201402     33

a                         201403     55

b                         201401     60

b                         201402     -

b                         201403     56

Situation is now, that with the "where not exists" records are not concatenated to the table.

Here's my code:

IF(vCheckPlanQvdExists) THEN

  Trace QVD exists, loading;

  Plan_Input:

  LOAD TERRITORY_ID,

    Monat,

      Value,

      TERRITORY_ID & Monat as Key

  FROM

..\QVD\edm_SF_Plan.qvd

  (qvd);

ENDIF

MONTHLY_TEMPLATE:

LOAD Distinct

  TERRITORY_ID,

  $(vCurrMonth) as Monat,

    TERRITORY_ID & $(vCurrMonth) as Key

FROM

..\QVD\edm_SF_opp_full.qvd

(qvd)

where len(trim(TERRITORY_ID)) > 0;

MONTHLY:

join (MONTHLY_TEMPLATE)

LOAD

  TERRITORY_ID,

  $(vCurrMonth) as Monat,

  //OLI_TMP.SERVICE_DATE_YEARMONTH//,

  sum(OLI_TMP.TCV_REPORTED) as Value

Resident OPP

where len(trim(TERRITORY_ID)) > 0

group by TERRITORY_ID, OLI_TMP.SERVICE_DATE_YEARMONTH;

IF(vCheckPlanQvdExists) THEN

  Trace Updating QVD;

  Concatenate (Plan_Input)

  LOAD

  Key,

  TERRITORY_ID,

  Monat,

  Value

  Resident MONTHLY_TEMPLATE where not exists (Key, Key);

ELSE

  Plan_Input:

  LOAD *, 'Y' as New Resident MONTHLY_TEMPLATE;

  drop Field New;

  TRACE Creating new QVD;

ENDIF

Thanks

1 Solution

Accepted Solutions
MVP
MVP

Re: "Where not exists" not working

Since you are doing a resident load, the key values do already exist, that's why the where not exists fails to add any data. You need to rename the key in your monthly data, then use the renamed field name as second argument to the exists function.

3 Replies
Not applicable

Re: "Where not exists" not working

Anyone able to help?

Thanks.

MVP
MVP

Re: "Where not exists" not working

Since you are doing a resident load, the key values do already exist, that's why the where not exists fails to add any data. You need to rename the key in your monthly data, then use the renamed field name as second argument to the exists function.

Not applicable

Re: "Where not exists" not working

That solution works, many thanks.

For my understanding: by enhancing the monthly table and the exists function with a new key (I called it KeyM by the way), the exists function checks for both key fields now, right?