Incremental Load of 2 Files

[cbaqir]

I am trying to do an incremental load on an existing QVD. I need to do for 2 different spreadsheets monthly. The first one works but the second one doesn't. I'm getting an error related to the temp MAX_DATE table not existing and I don't know what to do.

[cbaqir]

I am trying to append only new data to the QVD. Load the exisiting QVD, find max trx_date, pull new data from first spreadsheet, add to existing QVD, repeat for second spreadsheet. Here is my code:

Edit: the data has loaded without an error but it doesn't look right to me. I am looking at it now.

//3. Load the existing data from QVD file

PP_USAGE:
LOAD date(TRX_DATE, 'mm-dd-yyyy') as TRX_DATE,
// VERSION_PW_CAT_ID,
   // DESCRIPTION,
   FACILITY,
// as FACILITY_DISPLAY,
  // FACILITY as ORDER_VV,
   // FACILITY as PLAN_VV,
   // DOMAIN,
   %PP_DOMAIN_Key, //Join to ORD_USAGE
   //autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key, //Join to ORD_USAGE
   CNT_USAGE
//USAGE as CNT_USAGE
   // EXTRACT_DATE
FROM
[..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);
//[..\External_Data\PP_USAGE_ECISA.xlsx]
//(ooxml, embedded labels, table is CCG_PLANS);

//4. Find max date of QVD file

MAX_DATE:
LOAD
max(TRX_DATE) as Maxdate
resident PP_USAGE;

//5.Store the Maximum date in a variable.

//Let Maxdate = floor(peek('Maxdate', 0, 'MAX_DATE'));

Let Maxdate = floor(peek('Maxdate'));

//6. Drop the stored table

//drop table PP_USAGE;

////6. Drop the stored table
drop table MAX_DATE;

//7.Pull the rows that are new from `1DHO_EKG_PLAN_USAGE_CAREB.csv`.

NEW_USAGE_CAREB:
LOAD date(TRX_DATE, 'mm-dd-yyyy') as TRX_DATE,
// VERSION_PW_CAT_ID,
   // DESCRIPTION,
   FACILITY,
// DOMAIN,
   autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key,
// DESCRIPTION & '_' & DOMAIN as %DESC_DOMAIN_Key, //join to Regional Variability
   USAGE as  CNT_USAGE
// EXTRACT_DATE
FROM
[..\External_Data\1DHO_EKG_PLAN_USAGE_CAREB.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
where TRX_DATE > $(Maxdate);


//8. Concatenate the new values with existing qvd.

Concatenate
LOAD date(TRX_DATE, 'mm-dd-yyyy') as TRX_DATE,
FACILITY,
%PP_DOMAIN_Key,
CNT_USAGE
FROM
[..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);

//9. store date into existing qvd
store PP_USAGE into  [..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);



//Pull the rows that are new from `1DHO_EKG_PLAN_USAGE_ECISA.csv`.

//////// Powerplan Usage from QVD //////////
//3. Load the existing data from QVD file

PP_USAGE:
LOAD TRX_DATE,
// VERSION_PW_CAT_ID,
   // DESCRIPTION,
   FACILITY,
// as FACILITY_DISPLAY,
  // FACILITY as ORDER_VV,
   // FACILITY as PLAN_VV,
   // DOMAIN,
   %PP_DOMAIN_Key, //Join to ORD_USAGE
   //autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key, //Join to ORD_USAGE
   CNT_USAGE
//USAGE as CNT_USAGE
   // EXTRACT_DATE
FROM
[..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);
//[..\External_Data\PP_USAGE_ECISA.xlsx]
//(ooxml, embedded labels, table is CCG_PLANS);

//4. Find max date of QVD file

MAX_DATE:
LOAD
max(TRX_DATE) as Maxdate
resident PP_USAGE;

//5.Store the Maximum date in a variable.

//Let Maxdate = floor(peek('Maxdate', 0, 'MAX_DATE'));

Let Maxdate = floor(peek('Maxdate'));

//6. Drop the stored table

drop table MAX_DATE ;

//7.Pull the rows that are new from `1DHO_EKG_PLAN_USAGE_ECISA.csv`.

NEW_USAGE_ECISA:
LOAD TRX_DATE,
// VERSION_PW_CAT_ID,
   // DESCRIPTION,
   FACILITY,
// DOMAIN,
   autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key,
// DESCRIPTION & '_' & DOMAIN as %DESC_DOMAIN_Key, //join to Regional Variability
   USAGE as  CNT_USAGE
// EXTRACT_DATE
FROM
[..\External_Data\1DHO_EKG_PLAN_USAGE_ECISA.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
where TRX_DATE > $(Maxdate);


//8. Concatenate the new values with existing qvd.

Concatenate
LOAD TRX_DATE,
FACILITY,
%PP_DOMAIN_Key,
CNT_USAGE
FROM
[..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);

//9. store date into existing qvd
store PP_USAGE into  [..\QVD\01_Extract_QVD\PP_USAGE.qvd]
(qvd);

[prieper]

You do not need to drop the PP_USAGE.

PP_USAGE: LOAD .... FROM PP_USAGE.QVD;

MAXDATE: ...          // ok

LET MaxDate = .....     // ok

DROP TABLE MAXDATE;           // ok, cleaning up

CONCATENATE (PP_USAGE) LOAD .... FROM ECISA..... WHERE TRX_DATE > $(MaxDate);     // ok, new records appended

STORE PP_USAGE INTO .....

and you are done ....