topic incremental load for more than 1 file !!!!!!!!!!! in QlikView

incremental load for more than 1 file !!!!!!!!!!!

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

We are working on 1 scenario where after loading the initial data , we need to load multiple files but each and every file should load in incremental load manner like 1^st file than 2^nd file ans so on and follow the inser+update scenario.

I have already loaded qvd file and now i just want incremental scenerio ...insert and update ...

but while running below script only insert scenario is working not update.

please suggest how to make the scenario like :

every day file should load with qvd with insert and update and scenario and so on

like 1st file should be Basic_20170315 in qvd than Basic_20170316 with insert and update scenario and need to update the data as desired.

script code given below:

===============================================

for i = 20170315 to 20170317

vFileName = 'Basic_' & $(i);

Basic:

LOAD *

FROM

$(vPath)$(vFileName).xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD *

FROM

$(vPath)Basic.qvd

(qvd)

where not Exists(id);

Store Basic into $(vPath)Basic.qvd(qvd);

next i;

Re: incremental load for more than 1 file !!!!!!!!!!!

adamdavi3s — Fri, 24 Mar 2017 09:35:04 GMT

Your code looks fine, assuming that the spreadsheet contains the new and updated data?

Any new and modified rows will be uploaded, then Qlik will just load any rows which don't exist i.e. are not new or updated.

We need to know more about your data to advise further

Re: incremental load for more than 1 file !!!!!!!!!!!

qliklearnervir — Fri, 24 Mar 2017 13:07:18 GMT

yes...but i want that file should load like:

first it should load 20170315 file and concatenated with QVD and store.

after that 20170316 file and concatenated with QVD and store.

like each file should load separately...

Re: incremental load for more than 1 file !!!!!!!!!!!

adamdavi3s — Fri, 24 Mar 2017 13:24:32 GMT

This should be what your script is doing?

Where is it going wrong?

Re: incremental load for more than 1 file !!!!!!!!!!!

qliklearnervir — Fri, 24 Mar 2017 13:54:48 GMT

no it is not updating the record.

for example i got 20170315 data having id and status and in 20170316 file same id but status changed....

in this scenario this code will not work.....

this code will only append the data instead of update scenario...

i want some more programmatic code to load insert + update for n number of files....

Re: incremental load for more than 1 file !!!!!!!!!!!

adamdavi3s — Fri, 24 Mar 2017 14:05:14 GMT

Hmmm that is strange, in theory this is what is happening

Day1:

store to QVD

Day2:

load id from qvd where not exists

should only load

then store

back into qvd

Re: incremental load for more than 1 file !!!!!!!!!!!

vinieme12 — Fri, 24 Mar 2017 14:09:16 GMT

if Basic_20170316 has the latest data then Basic_20170314 should be loaded after Basic_20170316

reverse the order in which you are loading the files

Use Do loop Until instead of For loop to reverse the order

Re: incremental load for more than 1 file !!!!!!!!!!!

adamdavi3s — Fri, 24 Mar 2017 14:50:57 GMT

Figured it out, you need a drop table in there

Sorry that took me longer than it should have to work out.

This is a handy script which will also avoid any errors if the qvd doesn't exist

for i = 20170315 to 20170316
vFileName = 'basic_' & $(i);
Basic:
LOAD *
FROM
$(vPath)$(vFileName).xlsx
(ooxml, embedded labels, table is Sheet1);


LET logqvdfile = '$(vPath)Basic.qvd';
LET baseQVDFileSize = FileSize('$(logqvdfile)'); //test the file size


IF baseQVDFileSize > 0 THEN 
Concatenate
LOAD *
FROM
$(vPath)Basic.qvd
(qvd)
where not Exists(id);
ELSE 
ENDIF

Store Basic into $(vPath)Basic.qvd(qvd);

drop table Basic;
 next i;

LOAD *
FROM
$(vPath)Basic.qvd
(qvd);

Re: incremental load for more than 1 file !!!!!!!!!!!

Anil_Babu_Samineni — Sat, 25 Mar 2017 16:29:05 GMT

I am assuming, you are just storing into qvd. May be you need to fetch stored one by help of

load * from Updatedqvdpath;

Re: incremental load for more than 1 file !!!!!!!!!!!

adamdavi3s — Mon, 27 Mar 2017 06:06:03 GMT

This makes no sense and is not helpful

Re: incremental load for more than 1 file !!!!!!!!!!!

Anil_Babu_Samineni — Mon, 27 Mar 2017 06:23:57 GMT

Then what does the mean of your comment

LOAD *
FROM
$(vPath)Basic.qvd
(qvd);

Re: incremental load for more than 1 file !!!!!!!!!!!

adamdavi3s — Mon, 27 Mar 2017 06:26:55 GMT

Anil Babu Samineni wrote:

Then what does the mean of your comment

LOAD *

FROM

$(vPath)Basic.qvd

(qvd);

I've used exactly the code which the OP has used, and works and makes sense.

It is unnecessary to make any changes to this, when the code is fine

LOAD *

FROM

$(vPath)Basic.qvd

(qvd)

Re: incremental load for more than 1 file !!!!!!!!!!!

Anil_Babu_Samineni — Mon, 27 Mar 2017 06:29:09 GMT

True, that is my intend to show over here.

Re: incremental load for more than 1 file !!!!!!!!!!!

qliklearnervir — Wed, 19 Apr 2017 02:31:18 GMT

HI vineeth,

can you give me some code snapshot for use do loop until instead of for loop to reverse the order ....this is bit urgent.please help.

the code lgic is similar as given in above code.

Re: incremental load for more than 1 file !!!!!!!!!!!

vinieme12 — Wed, 19 Apr 2017 06:36:13 GMT

Try below

for i = 20170317 to 20170315 Step -1

vFileName = 'Basic_' & $(i);

Basic:

LOAD *

FROM

$(vPath)$(vFileName).xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD *

FROM

$(vPath)Basic.qvd

(qvd)

where not Exists(id);

Store Basic into $(vPath)Basic.qvd(qvd);

Drop table Basic;

next i;