topic Re: how to manage incremental load in QlikView

how to manage incremental load

sakshikaul — Wed, 25 Nov 2020 16:16:04 GMT

I have a scenario In which I will get full load for orders in csv format

i will also get records (incremental records ) ie only those records which have been changed on daily basis in csv format in same folder where full load is kept. The records which I am getting as an incremental load will be created on basis of date and time.

for eg I have two files

File Full Load contains all the records lets say

item order_no qty

A 1112879 20

B 1123445 5

c 2336788 9

file increment load will contain record as follows

A 11112879 4

so increment file (which is created on daily basis) will only contain one record (changed record ) not the rest records

now I want to load (full load) once and only the increment files on daily basis. so how to handle the same ?

LET vToday = date(today(), 'YYYYMMDD');

Full_Order_Data:
LOAD Plant,
OrderNo,
OrderDate,
OrderType,
DistChannel,
Division,
NCODE,
Season,
MatType,
Style,
Color,
Size,
OrderQty,
OrderValue,
ConfirmedQty,
ConfirmedValue,
MRP
FROM [D:\Qlik_OrderSheets\OrderSheets\QLIK*$(vToday)*.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

this above statement will only execute current files but not the full load so how to handle the same ?

please help @marcus_sommer

Re: how to manage incremental load

ahmed_hassan — Tue, 15 Jan 2019 08:17:01 GMT

Hi,

I am not sure if it is what you mean, but if you want to load the "full load" and update it based on the daily changed record as following:

item    order_no      qty 
A       1112879       4 
B       1123445       5
c       2336788       9

Then you have to use the update option:

FullLaod:
LOAD item,  
     order_no,
     qty
FROM
[..\ Server/daily report];

Concatenate(FullLaod)

LOAD item, 
     order_no,
     qty
FROM
[..\fullload file]
(qvd)
where Not Exists(item);

However, if you want to load the daily changed record only , you should use a loop (in case the name of the file is changing) or just use (Load * from path *.csv)

Good luck,

Ahmed

Re: how to manage incremental load

sakshikaul — Tue, 15 Jan 2019 10:04:15 GMT

I have understood this but I have a query. As we know we have to follow an ETL process so for that I have following folders :-

Extract, Transform, data modelling and final application.

My first qvw file will be present in Extract folder in which I will implement the same below scenario,so i need help here that whether I have to create the full load qvd in this application?

I have three full load files present in the same folder so do I have to to create qvd as follows?

note- The file in bold letters is the file created on daily basis

All those files where I have applied where conditions are full load files, so is this the right way to handle the same ?

LET vToday = date(today(), 'YYYYMMDD');

Full_Order_Data:

LOAD Plant,
OrderNo,
OrderDate,
OrderType,
DistChannel,
Division,
NCODE,
Season,
MatType,
Style,
Color,
Size,
OrderQty,
OrderValue,
ConfirmedQty,
ConfirmedValue,
MRP
FROM [D:\Qlik_OrderSheets\OrderSheets\QLIK*$(vToday)*.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

concatenate(Full_Order_Data)
LOAD Plant,
OrderNo,
OrderDate,
OrderType,
DistChannel,
Division,
NCODE,
Season,
MatType,
Style,
Color,
Size,
OrderQty,
OrderValue,
ConfirmedQty,
ConfirmedValue,
MRP
FROM
[D:\Qlik_OrderSheets\OrderSheets\QLIK20190110-074020-988.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
where exists(NCODE,OrderNo);

LOAD Plant,
OrderNo,
OrderDate,
OrderType,
DistChannel,
Division,
NCODE,
Season,
MatType,
Style,
Color,
Size,
OrderQty,
OrderValue,
ConfirmedQty,
ConfirmedValue,
MRP
FROM
[D:\Qlik_OrderSheets\OrderSheets\QLIK20190110-074031-308.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
where exists(NCODE,OrderNo);

LOAD Plant,
OrderNo,
OrderDate,
OrderType,
DistChannel,
Division,
NCODE,
Season,
MatType,
Style,
Color,
Size,
OrderQty,
OrderValue,
ConfirmedQty,
ConfirmedValue,
MRP
FROM
[D:\Qlik_OrderSheets\OrderSheets\QLIK20190110-074104-802.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Where not exists(NCODE,OrderNo);

store Full_Order_Data into C:\Users\dmsadmin\Desktop\New folder\QVD\extract_qvd\Full_Order_Data.qvd;
drop table Full_Order_Data;

Re: how to manage incremental load

sakshikaul — Mon, 21 Jan 2019 06:49:37 GMT

Do I need last updated date here ?

or without that only can work?

Like below-

Full_Order_Data:
LOAD Plant,
OrderNo,
OrderDate,
OrderType,
DistChannel,
Division,
NCODE,
Season,
MatType,
Style,
Color,
Size,
OrderQty,
OrderValue,
ConfirmedQty,
ConfirmedValue,
MRP
FROM
[D:\Qlik_OrderSheets\OrderSheets\QLIK*$(vToday)*.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

concatenate(Full_Order_Data)

LOAD Plant,
OrderNo,
OrderDate,
OrderType,
DistChannel,
Division,
NCODE,
Season,
MatType,
Style,
Color,
Size,
OrderQty,
OrderValue,
ConfirmedQty,
ConfirmedValue,
MRP
FROM
[D:\Qlik_OrderSheets\OrderSheets\Order_Data.qvd]
(qvd);
where not exists(NCODE,Style);
store Full_Order_Data into C:\Users\dmsadmin\Desktop\New folder\QVD\extract_qvd\Full_Order_Data.qvd;
drop table Full_Order_Data;

Please help @marcus_sommer and @ahmed_hassan

Re: how to manage incremental load

ahmed_hassan — Tue, 22 Jan 2019 07:51:38 GMT

Hi,

the best way to support you, is by solving 1 problem at a time (I will do my best 🙂 )

First of all, if the data source is only ".xlsx", ".csv" etc. Then you have to transform it into qvd (to improve performance)

SET anyname = '..\Path\'; // folder to store the extracted QVDs in

Full_Order_Data:
LOAD Plant *
FROM
[D:\Qlik_OrderSheets\OrderSheets\QLIK*$(vToday)*.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE Full_Order_Datainto [$(anyname )future_file_name.qvd] (qvd);

you can do the same for any other file.

Secondly, you can combine the QVDs or use the "update" solution in the 2nd transformation script

FullLaod:
LOAD *
FROM
[..\ path];  // the actual file

Concatenate(FullLaod)

LOAD *
[..\ the source]
(qvd)
where Not Exists(Primary key or any unique number);

STORE FullLoad[$(anyname )Fullload.qvd] (qvd);

Finally, you can use the FullLoad file (the final one) in the application (qwv).

Good luck!

Ahmed

Re: how to manage incremental load

sakshikaul — Tue, 22 Jan 2019 08:54:50 GMT

Hi,

According to solution provided by you I have implemented the same. Please help is it correct ?

LET vToday = date(today(), 'YYYYMMDD');
//full load master
Material_Master_full:
LOAD MATERIAL,
COLOR,
SIZE,
[Size Group],
Description,
COMPOSITION,
BRANDCD,
BRAND,
SEASON,
SEASONDESC,
THEME,
DELYCODE,
GENDER,
ProductHierarchy,
MaterialGroup,
[Core/Fashion],
FIT,
PATTERN,
[Key/NonKey],
RANGE,
ORIGIN,
[Top/Bottom],
SLEEVE,
NECK,
WEATHER,
OCCASSION,
CustomDesc,
MaterialGrp4,
MGRP4DESC,
EAN,
ALTEAN,
PurchaseGroup,
PurchaseGroupDesc,
CATEGORY,
SUBCAT,
BASECOMP,
MaterialType,
ValuationClass,
COST,
MRP,
ChangeDt
FROM
[D:\Qlik_Master\Material_master\Material_Master_full.qvd]
(qvd);

concatenate
//increment master files
LOAD MATERIAL,
COLOR,
SIZE,
[Size Group],
Description,
COMPOSITION,
BRANDCD,
BRAND,
SEASON,
SEASONDESC,
THEME,
DELYCODE,
GENDER,
ProductHierarchy,
MaterialGroup,
[Core/Fashion],
FIT,
PATTERN,
[Key/NonKey],
RANGE,
ORIGIN,
[Top/Bottom],
SLEEVE,
NECK,
WEATHER,
OCCASSION,
CustomDesc,
MaterialGrp4,
MGRP4DESC,
EAN,
ALTEAN,
PurchaseGroup,
PurchaseGroupDesc,
CATEGORY,
SUBCAT,
BASECOMP,
MaterialType,
ValuationClass,
COST,
MRP,
ChangeDt
FROM
[D:\Qlik_Master\Material_master\MAT_MAS_*$(vToday)*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq)
where not exists(MATERIAL&COLOR&SIZE);

store Material_Master_full into C:\Users\dmsadmin\Desktop\New folder\QVD\extract_qvd\Material_Master_full.qvd;

Re: how to manage incremental load

ahmed_hassan — Tue, 22 Jan 2019 09:28:41 GMT

Hi,

you should start the script with the table which contains the current records, then concatenate it with the table which contains all records.

>> where not exists(MATERIAL&COLOR&SIZE);

If the 3 fields are used as 1 primary key then you should do it as following: (keep in mind that the load part should be done for both tables to create the primary key)

Load*, MATERIAL&COLOR&SIZE as Primary

From ....

where not exists(Primary, MATERIAL&COLOR&SIZE);

Re: how to manage incremental load

sakshikaul — Tue, 22 Jan 2019 09:47:22 GMT

Hi,

If I follow this "you should start the script with the table which contains the current records, then concatenate it with the table which contains all records" then I am getting following result

whereas I want even to work on historical data so whatever I am having in full load I need that too. like below

Please help ??

Re: how to manage incremental load

ahmed_hassan — Tue, 22 Jan 2019 10:05:39 GMT

There is a mistake in your script somewhere. The solution should update the historical data only.
Please check the following link:
https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Re: how to manage incremental load

sakshikaul — Tue, 22 Jan 2019 10:20:18 GMT

This is the soluton and it's updating the historical data only.

LET vToday = date(today(), 'YYYYMMDD');
//full load master
Material_Master_full:
LOAD MATERIAL,
COLOR,
SIZE,
MATERIAL&COLOR&SIZE as ItemNumber,

ChangeDt
FROM
[D:\Qlik_Master\Material_master\MAT_MAS_*$(vToday)*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

concatenate
//increment master files
LOAD MATERIAL,
COLOR,
SIZE,
MATERIAL&COLOR&SIZE as ItemNumber,
ChangeDt
FROM
[D:\Qlik_Master\Material_master\Material_Master_full.qvd]
(qvd)

where not exists(ItemNumber,MATERIAL&COLOR&SIZE);

store Material_Master_full into C:\Users\dmsadmin\Desktop\New folder\QVD\extract_qvd\Material_Master_full.qvd

Re: how to manage incremental load

ahmed_hassan — Tue, 22 Jan 2019 11:13:37 GMT

Looks perfect, I assume that [D:\Qlik_Master\Material_master\MAT_MAS_*$(vToday)*.txt] contains the current records.

Re: how to manage incremental load

sakshikaul — Tue, 22 Jan 2019 11:18:11 GMT

yes

Re: how to manage incremental load

sakshikaul — Tue, 05 Feb 2019 09:45:02 GMT

I have a query if suppose on ftp the file is not created on basis of today's date then that particular file will be missed so how to handle that particular thing?