topic Re: Incremental load in QlikView

Incremental load

Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

How to perform incremental load on 250 tables in single reload.

if my tables are less then we can try with below code.in my case table count is 250 means I need to write 250 times ???

is there any better way to do this so that it can save my time.

1)I have total 250 tables in oracle.firstly i have converted tables into qvd's. it took 13 hours.it's ok np.

2)Now daily huge transaction data is adding daily now i need to do incremental load (insert only)

3)Is there any script that loop through 250 tables and perform incremental load.please guide me.

4)In community some one told we can achive by using sub-routine but i dont't know how to use.

----------------------------------------------------------------------------------------------------------------------------------------

The same thing i need to implement incremental for 680 from sql database (incremental load)

Please share any script which can perform incremental load dinamically.

Re: Incremental load

jmvilaplanap — Fri, 07 Jul 2017 18:25:32 GMT

Hi,

I don't understand exactly what do you mean. Do you have 250 excel files? Are always the same excel?

If you have 250 excel, do you know that you can use asterisks in the name of file? for example

LOAD

FROM sales*.xlsx ....

Regards

Re: Incremental load

Anonymous — Fri, 07 Jul 2017 18:48:41 GMT

You can use wildcards in your load from Excel like Jose says.

FROM Sales*.xlsx

or even

FROM *.xlsx

Or if you need to loop through the individual spreadsheets in 1 workbook, you can look at this.

Load excel, loop the worksheet

Re: Incremental load

datagrrl — Fri, 07 Jul 2017 18:59:53 GMT

I think Jose and Wallo might have the answer.

Do you have 250 Sales Tables to increment or 250 tables in your data model?

If that is the case you can probably write a script that loops through the 250 values dynamically.

If you have 250 tables in your data model, I would probably take a look there and see if you can clean it up. I have worked on some pretty complex projects, and 250 seems a bit excessive, but I am sure there is a case for it.

Re: Incremental load

Sat, 08 Jul 2017 05:46:26 GMT

Excuse me .

1)I have total 250 tables in oracle.firstly i have converted tables into qvd's. it took 13 hours.it's ok np.

2)Now daily huge transaction data is adding daily now i need to do incremental load (insert only)

3)Is there any script that loop through 250 tables and perform incremental load.please guide me.

4)In community some one told we can achive by using sub-routine but i dont't know how to use.

----------------------------------------------------------------------------------------------------------------------------------------

The same thing i need to apply for 680 sql database (incremental load)

Please share any script which can perform incremental load dinamically.

Re: Incremental load

Sat, 08 Jul 2017 08:08:51 GMT

Thanks wallo.

i'm loading from database first i have converted all tables into qvd's ( all qvd's have different names)

now i need to load only new data like Max(modified_Date)>Modified_Date.

Re: Incremental load

vinieme12 — Sat, 08 Jul 2017 09:12:20 GMT

If you have insert only and no updates you don't even need incremental load! just concatenate your tables!!

Re: Incremental load

vinieme12 — Sat, 08 Jul 2017 09:14:11 GMT

Also why do you have 250 tables! how much data are you trying to load(no of Rows) , for how many years?

Re: Incremental load

tajmohamed30 — Sat, 08 Jul 2017 09:27:09 GMT

before going into the incremental load concept can you please share your table viewer of your normal reload because need to check how it looks when loading with 250 tables or else you are saying for 250 columns of some tables

Re: Incremental load

Sat, 08 Jul 2017 09:48:23 GMT

Thanks Vineeth.

250 tables in data base .

My actual plan in:

1)Currently we have 250 tables in database and total 250 tables are converted to qvd format.

2)My total data size 400 GB.2 Years data(is because huge transaction world wide)

3)37 Qlikview applications are on live 37( qlikview&Qliksense) applications will load data from 250 tables

----------------------------------------------------------------------------------------------------------------------------------------------

Incremental load:(on daily basic)

For loading this 400 gb of data it took more time for me.so we are planning incremental load only new data should loaded instead of loading entire data from the data base.

only new data for eg:

if i have loaded 07-jul-2017 then next day i should load only (08-Jul-2017) or 09-Jul-2017)

like it should load greater then last reload data and time(my data is in DD-MM-YYYY HH-MM-SS)

below code i used for Incremental load(sample code i found in google)

This code is working perfect for me .

problum is i need to write this code for each and every table like 250 tables how to optimist this reload by writing any loop or dynamic script.

There is other database of that also we need to incremental load it contain 600 tables.

please share any dynamic script available to load this table at time

Re: Incremental load

Sun, 09 Jul 2017 18:18:15 GMT

Hi Randi,

i have total 250 tables i have converted them qvd format.then i need to refresh this qvd's daily.so i chosed incremental load.

As you told i need to write a script that loops through the 250 Qvd's (qvd's names are different)

Please guide how to write that loop script

Re: Incremental load

vinieme12 — Mon, 10 Jul 2017 08:06:59 GMT

if all your QVD's are in a single folder then just the below will do

LOAD *

FROM

[Fullpath\*.qvd]

(QVD);

Re: Incremental load

Anonymous — Mon, 10 Jul 2017 15:14:04 GMT

It sounds like you're looking for something more like this:

//Where files exist

LET File_Path = chr(39) & '..\Data\QVD\' & chr(39)

;

TableList:

LOAD * INLINE [

Table_Name

Sales

Products

Employees

Locations

];

For Each vTable in FieldValueList('Table_Name')

let vQVDName = chr(39) & $(File_Path) & vTable & '.qvd' & chr(39);

let vFileName = chr(39) & $(File_Path) & vTable & '.xlsx'& chr(39);

//Get Max Modified Date. using where not exists pulls only distinct dates

Temp:

Load

Modified_Date

from

$(vQVDName)

(qvd)

where not exists(Modified_Date);

Last_Updated_Date:

load

max(Modified_Date) as MaxDate

Resident Temp;

let Last_Updated_Date = peek('MaxDate',0,'Last_Updated_Date');

Drop table Temp;

//Begin incremental

Incremental:

Load *

From $(vFileName)

(ooxml, embedded labels, table is Sheet1)

where Modified_Date> $(Last_Updated_Date);

Concatenate

load *

From

$(vQVDName)(qvd);

Store Incremental into $(vQVDName)(qvd);

Next;