topic Incremental Load in QlikView

Incremental Load

mattdt1811 — Fri, 15 Nov 2024 23:53:22 GMT

Hi All,

I was hoping someone would be able to take a look at my incremental load scenario below and advise why my script for it is not working please?

My scenario is:

I am looking to analyse our delivery notes raised and the date the delivery is requested for.

In our database for example we have the fields;

Delivery Note Number	Requested Delivery Date
12345	10/09/2020
12346	11/09/2020
12347	11/09/2020

On our IT system, users can amend the Requested Delivery Date. The problem is in our database, the record for Requested Delivery Date is updated, not a new record inserted.

I need to capture daily only those Delivery Note Numbers where the Requested Delivery Date has changed from the previous days, as well as new records, and get a new record for each inserted into my QVD.

My current process:

I am using a concatenation of “Delivery Note Number”&“Requested Delivery Date” (so for example 1234510/09/2020) as my unique key for each field.

So each day I get the data, taking the new Key Field and trying to get it to insert those that do not already exist in my QVD.

Attached Script:

1) Initial Incremental Load

This is my first grab of our data and my base table to check if record already exists
This performs my SQL, getting my fields and creating my key field and stores as DATA_Incremental.QVD
This reload I have only done the once, it is not on a reload schedule

2) Scheduled Incremental Load

This reloads every day and grabs the same data as the above
Concatenates with the DATA_Incremental.QVD and inserts only those records where the key fields does not exist.
It then saves it as my Incremental_To_Use.QVD (this is the one I am using)

Thanks in advance.

Re: Incremental Load

edwin — Thu, 01 Oct 2020 19:27:35 GMT

the logic looks sound to me. of course teh command

STORE TABLE INTO DATA_Incremental.QVD;

was just a typo rt?

can you pls describe what the outcome is that you werent expecting? why do you think your logic isnt working?

Re: Incremental Load

mattdt1811 — Mon, 05 Oct 2020 08:36:46 GMT

Hi Edwin,

Thanks for your reply.

I don't believe there is any typo as my various reports update all ok.

It is very strange because I have a few instances on one of the very first reloads of "Scheduled Incremental Load" where it appears to have worked fine in another testing app I have but since then I only get one instance of each note number for subsequent reloads.

You can see from the attached there have been 16,000+ notes raised since the last one it captured correctly.

Is there perhaps some different logic I can try for my incremental load?

Thanks.

Re: Incremental Load

Brett_Bleess — Thu, 22 Oct 2020 10:53:33 GMT

Dropping the Help Doc for Incremental Load via QVD files, not sure whether you reviewed this or not, should provide some clarifications on how to approach things properly:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/QVD_Incremental.htm

Including a Design Blog post too, it is geared toward Sense, but it may be helpful too:

https://community.qlik.com/t5/Qlik-Design-Blog/Overview-of-Qlik-Incremental-Loading/ba-p/1466780

Regards,
Brett

Re: Incremental Load

mattdt1811 — Thu, 22 Oct 2020 11:17:11 GMT

Hi Brett,

Thanks for the links.

A contact at our Qlik partner was able to sort me out with the correct script (example below).

Will drop it here just in case someone else finds it useful. Seems to be working ok in the first few days.

// Original Data
RawData:
LOAD
NoteNumber &'|'&RequestedDate as Key1,
NoteNumber,
RequestedDate,
reloadDate
FROM
RawData.txt
(txt, utf8, embedded labels, delimiter is ',', msq);

// SQL extract New records
NoConcatenate
RawDatatemp:
load *,NoteNumber &'|'&RequestedDate as Key, Timestamp((now(1))) as reloadDate
Inline
[NoteNumber, RequestedDate
3, 01/10/2020
4, 20/10/2020
5, 20/12/2020
6, 27/11/2020
7, 13/11/2020
]
where RequestedDate> date(now()) ;

// Check if in the original QVD
Concatenate(RawData)
//RawData:
load *,Key as Key1 Resident RawDatatemp
where not Exists(Key1, Key);

// clean up
drop table RawDatatemp;

drop field Key;

store RawData into RawData.txt (txt);

Re: Incremental Load

cbaqir — Mon, 23 Nov 2020 16:16:31 GMT

I cannot figure out why my QVD is not updating with new data. It looks like it works and the QVD even increases in size but the new data doesn't show up. Any diea?

https://community.qlik.com/t5/QlikView-Scripting/QVD-not-updating-with-appended-data/td-p/1762287

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

////New Incremental Load
//
//
////
//////check if source file exist
////

if $(vFileExists)= 0 then

//file exists

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) ;
//[..\QVD\01_Extract_QVD\Fired.qvd](qvd)
//and WildMatch(POSITION, '*PFS*')=0

store Fired into ..\QVD\01_Extract_QVD\Fired.qvd (qvd);
//**/

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

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\";