topic Re: incremental load in QlikView

incremental load

Anonymous — Mon, 23 Nov 2015 13:11:32 GMT

Hi All,

I am new to Qlikview and have a requirement like below.

Daily I am getting two excel files namely account_inserts and account_updates(these files will be override on daily basis with latest data) which don't have any time stamp columns and having only ID column. Finally I want to combine two files as single as account.QVD.

On daily it should append only current month data only not previous months records.

I know incremental load, but this is something different requirement as I am feeling

Please help me on this issue.

Regards,

Kumar

Re: incremental load

Clever_Anjos — Mon, 23 Nov 2015 13:17:11 GMT

Maybe (pseudo code)

//Load your updates
LOAD *
FROM [account_updates.xlsx](ooxml, embedded labels, table is Plan1);

If FileSize('yourqvd.qvd') > 0 then
//Load your records that are not updated
LOAD *
FROM [yourqvd](qvd)
WHERE NOT Exists(someKeyField);
endi;f


//Load your inserts
LOAD *
FROM [account_inserts .xlsx](ooxml, embedded labels, table is Plan1);


store table into [yourqvd](qvd);

Re: incremental load

Anonymous — Mon, 23 Nov 2015 13:21:39 GMT

Hi,

Thanks for providing code.

In this code I have seen qvd file being included. When you do very first time we don't have this file? If I run this code it will throw error.

Regards,

Kumar

Re: incremental load

Anonymous — Mon, 23 Nov 2015 13:23:59 GMT

You can use Binary load in this scenario else where

Open Qlikview > Edit Script> fetch both file into the edit script> name the tables . concatenate both the tables. and Store it as a QVD . then use windows scheduler to automatically get a qvd for you.

if you want me to guide for window scheduler do let me know.

Re: incremental load

santhosh_k_n — Mon, 23 Nov 2015 13:24:39 GMT

Hi,

As mentioned, there is no time stamp/date field in the xls files ur receiving, i suggest to save the QVD with a time stamp say temp_23112015.qvd.

and you can manullay(or can define scripts to) deleate or copy the previous month QVDs into a another folder so that only current month QVD files get loaded.

Hope this is usefull

BR, SK

Re: incremental load

HirisH_V7 — Mon, 23 Nov 2015 13:26:36 GMT

Hi,

You can use only *.xlsx As Filename. This will pick all the files which are in the folder .This Data will append automatically and get updated if the Header are same in all excels and You can use the where condition for month field.

It can be like this,

LOAD *

FROM

[\*.xls]

(biff, embedded labels, table is Sheet1$);

Hope this helps,

regards ,

Hirish

Re: incremental load

Anonymous — Mon, 23 Nov 2015 13:30:37 GMT

Hi,

Can anyone provide me with example. I tried with excel files overriding and qvd also overriding.

Regards,

Kumar

Re: incremental load

Clever_Anjos — Mon, 23 Nov 2015 13:40:10 GMT

I´ve updated the code

Re: incremental load

Anonymous — Mon, 23 Nov 2015 13:57:14 GMT

Thanks clever and it is working fine.

Re: incremental load

Clever_Anjos — Mon, 23 Nov 2015 16:27:13 GMT

Good to know