Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

QVD not updating with appended data

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

Labels (3)
2 Replies
cbaqir
Specialist II
Specialist II
Author

@sunny_talwar Any idea?

Joseph_Musekura
Support
Support

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