My script partially works. It's supposed to check if there is a new file and if there is, move it to the right External Data subfolder, update the QVD, open the new QVD and then move the used files to an OLD folder.
The files are moved to the right subfolder and I don't think the QVD is appended or the QVD isn't really being updated because the max TRX_DATE is not 11/10+ and I am not sure why.
Any idea?
//SET vFileName = '..\..\1dho_uxp_ekm*.csv'; //local inc module
SET vFileName = '..\..\..\1dho_uxp_ekm*.csv'; //PROD inc module
LET vFileExists = ISNULL(FileSize('$(vFileName)'));
//LET vFileExists = NOT ISNULL(FileSize('$(vFileName)'));
LET FileDestination = '..\External_Data\';
//open existing QVD
Fired:
LOAD EXP,
FIRING_RECORD,
EXP & '-' & RULE as %KEY_DOMAIN_RULE, //join to Rule table
RULE,
ALERT_DT_TM,
date(TRX_DATE, 'MM/DD/YYYY') as TRX_DATE,
// date(MonthName(TRX_DATE),'MM-YY') as TRX_DATE_MMYY,
RULE & '-' & date(MonthName(TRX_DATE),'MM-YY') as %KEY_RULE_TRX_DATE,
TRX_TIME,
ALERT,
FACILITY,
POSITION,
NURSE_UNIT,
ENCNTR_PRSNL_RELTN,
ALERT_RECIPIENT
FROM
[..\QVD\01_Extract_QVD\Fired.qvd]
(qvd)
where WildMatch(POSITION, '*PFS*')=0 ;
//Load Modules files
EKS_MODULE_AUDIT:
LOAD MODULE_NAME as RULE,
if (wildmatch(MAINT_VALIDATION,'*EXPIRED*','*TESTING*'),'Inactive','Active') as RULE_STATUS,
// MODULE_NAME & '-' & date(MonthName(ALERT_DT_TM),'MM-YY') as %KEY_RULE_TRX_DATE,
EVOKES,
PURPOSE,
EXPLANATION,
CITATION,
QUERY,
IMPACT,
UPDT_DT_TM,
MAINT_INSTITUTION,
MAINT_AUTHOR,
MAINT_SPECIALIST,
DOMAIN
FROM
[..\External_Data\1dho_uxp_ekm_modules_*.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
////New Incremental Load
//////check if source file exist
////
if $(vFileExists)= 0 then
//file exists so move files from main folder to External_Data folder
execute cmd.exe /c move /y "$(vFileName)" "$(FileDestination)";
//
//////load to previous data from QVD
Concatenate (Fired)
LOAD EXP,
FIRING_RECORD,
EXP & '-' & MODULE_NAME as %KEY_DOMAIN_RULE, //join to Rule table
MODULE_NAME as RULE,
ALERT_DT_TM,
date(ALERT_DT_TM, 'MM/DD/YYYY') as TRX_DATE,
// date(MonthName(TRX_DATE),'MM-YY') as TRX_DATE_MMYY,
MODULE_NAME & '-' & date(MonthName(ALERT_DT_TM),'MM-YY') as %KEY_RULE_TRX_DATE,
time(ALERT_DT_TM, 'MM/DD/YYYY') as TRX_TIME,
ALERT_TEMPLATE as ALERT,
FACILITY,
POSITION,
NURSE_UNIT,
ENCNTR_PRSNL_RELTN,
ALERT_RECIPIENT
FROM
[..\External_Data\1dho_uxp_ekm_alert_audit_*.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
where NOT EXISTS(FIRING_RECORD) and WildMatch(POSITION, '*PFS*')=0 ;
//[..\QVD\01_Extract_QVD\Fired.qvd](qvd)
// WHERE NOT EXISTS(FIRING_RECORD);
store Fired into ..\QVD\01_Extract_QVD\Fired.qvd (qvd);
//**/
//
ELSE
EKS_MODULE_AUDIT:
LOAD MODULE_NAME as RULE,
if (wildmatch(MAINT_VALIDATION,'*EXPIRED*','*TESTING*'),'Inactive','Active') as RULE_STATUS,
EVOKES,
PURPOSE,
EXPLANATION,
CITATION,
QUERY,
IMPACT,
UPDT_DT_TM,
MAINT_INSTITUTION,
MAINT_AUTHOR,
MAINT_SPECIALIST,
DOMAIN
FROM
[..\External_Data\1dho_uxp_ekm_modules_*.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
////CB Move files once loaded
//
//
end if
execute cmd.exe /c move /y "..\External_Data\1dho_uxp_ekm_alert*.csv" "..\External_Data\OLD\";
MasterCalendar:
LOAD date(left(TRX_DATE,10), 'MM/DD/YYYY') as TRX_DATE,
date(MonthName(left(TRX_DATE,10)),'MM-YY') as TRX_DATE_MMYY,
date(TRX_DATE, 'MMM-YYYY') as MonthYear, //cb
Year(TRX_DATE) as Year,
Month(TRX_DATE) as Month,
WeekStart(TRX_DATE) as Week,
Day(TRX_DATE) as Day;
LOAD DISTINCT TRX_DATE
FROM ..\QVD\01_Extract_QVD\Fired.qvd (qvd);
