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);
