Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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.

View solution in original post

3 Replies
Not applicable
Author

Anyone able to help?

Thanks.

swuehl
MVP
MVP

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
Author

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?