QVD not updating with appended data

cbaqir — Fri, 15 Nov 2024 23:37:17 GMT

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

Re: QVD not updating with appended data

cbaqir — Tue, 08 Dec 2020 14:29:22 GMT

@sunny_talwar Any idea?

Re: QVD not updating with appended data

Joseph_Musekura — Wed, 16 Dec 2020 13:36:13 GMT

Hi,

I am Joseph Musekura (Talk to Experts Tuesday ).
From your explanation during the session, I think that we should do more investigations.

Can you please run the qvw again and collect the scripts (after scrambling the data if required) ?
In addition to the scripts, attach the related QVD and the used Qlikview version(EX 12.30.20300).
Is it possible for you to submit this case to Qlik Support?.

note:
you can scramble the data before generating the QVD.
/joseph

topic QVD not updating with appended data in QlikView

QVD not updating with appended data

Re: QVD not updating with appended data

Re: QVD not updating with appended data