topic Re: How to handle incremental load ? in QlikView

How to handle incremental load ?

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

Hi All,

I have to implement incremental load and I have following material master on FTP now how to handle incremental load? Here itemnumber is a primary key

Material_Master:

LOAD

MATERIAL,

COLOR,

SIZE,

MATERIAL&COLOR&SIZE as itemnumber,

[Size Group],

Description,

COMPOSITION,

BRANDCD,

BRAND,

SEASON,

SEASONDESC,

THEME,

DELYCODE,

GENDER,

IF(GENDER ='LADIES', 'WOMEN', IF(GENDER='MENS','MENS', IF(GENDER='BOYS' or GENDER='GIRLS' or GENDER='BABY','KIDS', GENDER))) as Gender_Revised,

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 as Promotional_1,

ValuationClass,

COST,

MRP as MRP_INR,

if (MRP <1000, '<1000', if (MRP <2000, '1000-2000',if (MRP <3000, '2000-3000',if (MRP <4000, '3000-4000',if (MRP <5000, '4000-5000',if (MRP <6000, '5000-6000', '>6000')))))) as [MRP Bucket]

FROM

(qvd)

where Exists itemnumber;

Re: How to handle incremental load ?

crusader_ — Wed, 11 Apr 2018 05:58:41 GMT

Hi,

Depending on your requirements there could be different scenarios:

1. Insert only

2. Insert + Update + Delete

1 Scenario:

Old_table:

LOAD * FROM *.qvd (qvd);

New_table:

LOAD * FROM *.qvd (qvd)

Where not Exists(UniqueID);

2 Scenario:

New_table:

LOAD * FROM *.qvd (qvd);

Old_table:

LOAD * FROM *.qvd (qvd)

Where not Exists(UniqueID);

NOTE: not Exists statement should be applied only to Unique Identifier, otherwise you'll get result different than expected.

Id you're trying to find only entries to update, than for your case should be smth like below:

Old_table:

Load

.....

itemnumber,

....

FROM Old.qvd (qvd);

New Table:

Load

...

Where Exists(itemnumber, MATERIAL&COLOR&SIZE);

itemnumber field should exist in your data model before you use it in Exists() statement.

Hope this helps.

//Andrei

Re: How to handle incremental load ?

Anonymous — Wed, 11 Apr 2018 06:20:48 GMT

I want to use a scenario where i can insert and update. I have different sources from where I am getting data.

1.store details from FTP.

2. Material Master from Ftp

3. Sales data from sql tables and

4, transaction file from FTP.

Now I want to implement incremental load. Firstly I was taking full load of Material files from FTP and now I am getting incremental files on FTP so Now please tell How to handle the incremental load in my qlikview so that the data can be inserted and overwritten?

Following is the data model

i am facing problem that i am getting duplicate records in my dasboard where i am using straight table

Re: How to handle incremental load ?

crusader_ — Wed, 11 Apr 2018 06:43:55 GMT

To be sure I understood correctly:

You loaded full table some time ago via FTP, and now receiving only incremental parts via FTP (for example last 1-2-3 days/hours)

In this scenario we're assuming that this new FTP has the most recent values, so you need to proceed with several transform steps:

1. For first run (with full) you need to store this table into qvd file... This is something you will re-use again and again.

Ex:

Table:

LOAD

....

MATERIAL&COLOR&SIZE as itemnumber,

....

FROM ftp://<path>/file.qvd (qvd);

Store table into <local path/ place>/full_file.qvd (qvd);

//// This local file will contain your full data, which you will update in the next steps (make a backup of this from to time - will reduce you a lot of headache)

2. For the rest following runs you need to read new file first, add to this table only missing entries from full table and then rewrite full.qvd file

Ex:

IncrementSales:

Load

.....

MATERIAL&COLOR&SIZE as itemnumber,

....

FROM ftp://<path>/Incremental_file.qvd (qvd);

///Here could be Concatenate statement if you're not sure that the table structure is always the same

Load * from <local path/ place>/full_file.qvd (qvd)

Where not exists(itemnumber);

store IncrementSales into <local path/ place>/full_file.qvd (qvd);

Then you can use updated <local path/ place>/full_file.qvd in your presentation app. Anyway after above steps IncrementSales table will contain only unique updated entries.

Hope this helps.

//Andrei

Re: How to handle incremental load ?

Anonymous — Wed, 11 Apr 2018 07:54:59 GMT

What u understood is absolutely correct and I have also understood your reply but i have a query as i am delaing with the business of clothing which is season based so I have Material master for different seasons present on FtP and i am following an ETL process so if I am using full load should i add in extract folder and take the backup

Re: How to handle incremental load ?

Anonymous — Wed, 11 Apr 2018 08:12:46 GMT

Where to implement 2nd step? is it in extract folder or transform folder ?

Re: How to handle incremental load ?

crusader_ — Thu, 12 Apr 2018 00:24:29 GMT

Hi,

Extract and transform folders contain qvw files, right? If so, than don't mix qvd and qvw, if not - then you can use extract folder for step 1 only once, and then everything should be in transform for step 2, therefore killing two birds with one stone - original full file always will be stored in extract folder and untouched (i.e. backup), modified file will be stored separately in transform (which also could be backed up manually or using scripting).

Anyway you can create sub-folders within QVD folder if required to follow the same procedure.

Regarding seasonality, again, do not mix everything together - extract full tables separately, for example Material_season1_2018.qvd, Material_season2_2018.qvd etc...

then in your transform apply step 2 and store modified qvds as for ex. Material_season1_2018_inc.qvd.

NOTE: You should read your initial file Material_season1_2018.qvd only once !!! otherwise you'll overwrite your changes.

Yes, you'll get a number of extra files, but it gives you flexibility to read only specific portion of data without any search (where) clauses - so always to be optimized.

Then you can use loop or * for loading multiple qvd files.

Anyway it's up to you to decide to how to proceed in your particular situation.

Good luck.

//Andrei

Re: How to handle incremental load ?

Anonymous — Thu, 12 Apr 2018 05:05:56 GMT

I have extract full load from Ftp in extract folder and have taken a backup for the same . Now I have several incremental files which are created on daily basis on Ftp and which will be overwritten so now should I load all of them to Transform(qvd Folder)????

Re: How to handle incremental load ?

crusader_ — Thu, 12 Apr 2018 05:28:15 GMT

No, you should copy everything from FTP to your local server

take FULL and couple INCREMENTAL files and try to do this in "Sandbox" (i.e. separately), until you feel yourself comfortable with solution.

Ultimately, it doesn't matter how you call folders and where you place QVD files - concept will work anyway.

1. Load * from your FULL qvd file and store it with different name X in the same place (you will update this copy, but not original one). (Or just copy-paste file)

2. Load inc1 file and concatenate with just created X qvd WHERE NOT EXISTS (UniqueID);

3. Store resulting table into X qvd - overwrite changes;

4. Load inc2 file and concatenate with just updated X qvd WHERE NOT EXISTS (UniqueID);

5. Store resulting table into X qvd - overwrite changes;

....

conitnue with steps 4. and 5. infinitely.

Your X qvd file will contain updated data.

Hope this helps.

Andrei

Re: How to handle incremental load ?

Anonymous — Thu, 12 Apr 2018 06:59:18 GMT

this where condition i have to implement in old qvd(data dump ) or new inc1 file?

Re: How to handle incremental load ?

crusader_ — Thu, 12 Apr 2018 07:03:09 GMT

ResultingTable:

LOAD * FROM new.qvd (qvd);

LOAD * FROM X.qvd (qvd)

WHERE NOT EXISTS(UniqueID);

*UniqueID in your case is itemnumber

//Andrei

Re: How to handle incremental load ?

Anonymous — Thu, 12 Apr 2018 07:09:05 GMT

So each time I have to load all the new icr files and concatenate in old files? rite?

Re: How to handle incremental load ?

crusader_ — Thu, 12 Apr 2018 09:01:31 GMT

Yes, that's right.... sequentially adding new data to full and overwrite full.

inc1 + old => old,

inc2 + old => old,

etc.

Andrei

Re: How to handle incremental load ?

Anonymous — Thu, 12 Apr 2018 11:21:06 GMT

I didnt get the last point. How to implement the last step ? ie overwrite full?

Re: How to handle incremental load ?

crusader_ — Thu, 12 Apr 2018 22:58:16 GMT

yes, otherwise how are you going to keep updated full file?

You will not keep all inc files forever, right? so you need a have common place to store changes, so full is your common place.

That's why I said you need to split your initial full and work on copy.

//Andrei

Re: How to handle incremental load ?

Anonymous — Fri, 13 Apr 2018 05:34:51 GMT

I have implemented the same but still not getting the correct figures. i have a field named gender, which i have pulled after implementing following above scenario., so please tell me is this incorrect data because the values in gender feilds present in files on FTP are not correct right ?

Re: How to handle incremental load ?

crusader_ — Fri, 13 Apr 2018 06:04:13 GMT

If you don't have any modifications on GENDER field, than most likely one of files contains this.

It couldn't appear just from nowhere or in the result of incremental load

You can load qvd file separately to check what is there or use EasyQlik QViewer tool.

//Andrei

Re: How to handle incremental load ?

Anonymous — Fri, 13 Apr 2018 08:40:47 GMT

orrected?

If I am selecting D under Gender field the chart is not showing any values. This means there is some deliminiter issue in master files? rite?

Re: How to handle incremental load ?

crusader_ — Fri, 13 Apr 2018 09:27:38 GMT

Could you just copy and open file?

It might be delimiter issue as well as bad data... hard to guess without seeing data...

//Andrei

Re: How to handle incremental load ?

Anonymous — Fri, 13 Apr 2018 10:10:25 GMT

So, i can assume due to that I am Not getting correct data?