Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
sakshikaul
Creator II
Creator II
‎2019-01-15 02:42 AM

how to manage incremental load

Hi 

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

1,712 Views
0 Likes
1 Solution

Accepted Solutions
sakshikaul
Creator II
Creator II
‎2019-01-22 05:19 AM
Author

In response to ahmed_hassan

Hi 

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.qvdpaint.png

View solution in original post

1,416 Views
0 Likes
12 Replies
ahmed_hassan
Contributor III
Contributor III
‎2019-01-15 03:17 AM

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

 

 

1,513 Views
0 Likes
sakshikaul
Creator II
Creator II
‎2019-01-15 05:04 AM
Author

In response to ahmed_hassan

Hi

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;

 

 

 

 

 

1,500 Views
0 Likes
sakshikaul
Creator II
Creator II
‎2019-01-21 01:49 AM
Author

In response to ahmed_hassan

Hi 

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

1,465 Views
0 Likes
ahmed_hassan
Contributor III
Contributor III
‎2019-01-22 02:51 AM

In response to sakshikaul

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

1,435 Views
0 Likes
sakshikaul
Creator II
Creator II
‎2019-01-22 03:54 AM
Author

In response to ahmed_hassan

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;

 

 

1,433 Views
0 Likes
ahmed_hassan
Contributor III
Contributor III
‎2019-01-22 04:28 AM

In response to sakshikaul

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

 

 

1,427 Views
0 Likes
sakshikaul
Creator II
Creator II
‎2019-01-22 04:47 AM
Author

In response to ahmed_hassan

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 

Untitled.png

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

paint.png

Please help ??

1,424 Views
0 Likes
ahmed_hassan
Contributor III
Contributor III
‎2019-01-22 05:05 AM

In response to sakshikaul
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/
1,419 Views
0 Likes
sakshikaul
Creator II
Creator II
‎2019-01-22 05:19 AM
Author

In response to ahmed_hassan

Hi 

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.qvdpaint.png

1,417 Views
0 Likes