Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Anyone able to help?
Thanks.
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.
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?