topic Re: Re: Incremental load approach in QlikView

Incremental load approach

Mon, 22 Sep 2014 13:08:01 GMT

Hi,

I'm currently building an app loading data from hundreds of Excel files.

One new file each date containing about 10,000 rows. I'm now about to enable incremental load in my script but not sure which approach to use. Is it for instance possible to exclusively load/add "todays file"? Last modified file etc?

I guess using unique ID-keys (I have to put them toghether myself) searching through millions of fields would be about as time consuming as doing a full reload?

How would you solve it?

Thanks in advance,

Olle

Re: Incremental load approach

Mon, 22 Sep 2014 13:21:49 GMT

Hi,

in this case , Incremental load resolves time consuming on load.

this can be done in three stages as per the requirement.

STAGE 1: only load new records into the document

STAGE 2: adding new records along with updated records in the Live data

STAGE 3: load records which are newly added, updated and deleted on Live data

Pls refer this QVW file for three stages.

This will help you .

Regards

Charitha.

Re: Incremental load approach

Anonymous — Mon, 22 Sep 2014 13:23:21 GMT

Sure , you can do it.

Go throw the file storage, find the latest one with FileTime function and load to the QVD (Concatenate with previously loaded data)

Re: Re: Incremental load approach

Mon, 22 Sep 2014 14:02:43 GMT

Thanks for your answer Lakshmi.

Looks good! However, I do not have an ID-field in my source data. I need to asseble one myself as per below.

Date & ':' & OriginalTime & ':' & Reference & ':' & SettlementAmount As TransactionKey

This prevents me from executing the IncrLOad in stage 1, since TransactionKey is not an actual field in the source doc.

Any idea how to get around this?

Thanks in advance,

Olle

Re: Re: Incremental load approach

Mon, 22 Sep 2014 14:17:04 GMT

all the concatenated fields are existing in the Live data souce ?

- Charitha

Re: Re: Incremental load approach

Mon, 22 Sep 2014 14:22:40 GMT

All of the fields are existing except a unique ID-field (called OrderID in your example). That is why I need to create one myself.

-Olle

Re: Re: Incremental load approach

Mon, 22 Sep 2014 14:57:29 GMT

yes , at STAGE 1 we have conditional field called Orderdate only .though it might be combination of all fields you can write the below sample for STAGE1

TableName:

Load *, Date & ':' & OriginalTime & ':' & Reference & ':' & SettlementAmount As TransactionKey

where DateField <= ' <Date>';

Re: Re: Incremental load approach

Mon, 22 Sep 2014 15:07:27 GMT

Thanks for your answer.

However not sure I'm following

The problem occurs because TransactionKey is not an actual field in the document.

FROM
[IncrementalLoadTest\2014-01-02.xls](ooxml, embedded labels, table is Incoming)
WHERE Date(Date,'MM/DD/YYYY') >= '$(vMax)' and Not Exists(TransactionKey);

Re: Re: Incremental load approach

Mon, 22 Sep 2014 15:07:56 GMT

in my source data document that is!

Re: Re: Re: Incremental load approach

Mon, 22 Sep 2014 15:18:57 GMT

what i am trying to say is

Load *, Date & ':' & OriginalTime & ':' & Reference & ':' & SettlementAmount As TransactionKey

FROM
[IncrementalLoadTest\2014-01-02.xls](ooxml, embedded labels, table is Incoming)
WHERE Date(Date,'MM/DD/YYYY') >= '$(vMax)' and Not Exists(TransactionKey);

this will works rght in you case ?

Re: Re: Re: Re: Incremental load approach

Tue, 23 Sep 2014 09:14:29 GMT

Thanks for your patience Lakshmi,

Now my script looks as per below now, but seems to be some kind of syntax error here. I'm doing something wrong obviously.

InitialOrders:

LOAD Beneficiary,
    Date,
    OriginalTime,
    Reference,
    Type,
    SettlementAmount,
    Date & ':' & OriginalTime & ':' & Reference & ':' & SettlementAmount As TransactionKey

FROM
[IncrementalLoadTest\2014-01-02.xls]
(ooxml, embedded labels, table is Incoming);
Store InitialOrders into Orders.qvd(qvd);
drop table InitialOrders;

//-------------------------------------------------------------------STAGE 1 -------------------------------------------

OrdersOVQ:
LOAD Beneficiary,
    Date,
    OriginalTime,
    Reference,
    Type,
    SettlementAmount,
    TransactionKey

FROM
Orders.qvd
(qvd);
LET vMax=Peek('Date',-1,'OrdersOVQ');

//drop table OrdersOVQ;

IncrLOad:
LOAD Beneficiary,
    Date,
    OriginalTime,
    Reference,
    Type,
    SettlementAmount,
    Load *, Date & ':' & OriginalTime & ':' & Reference & ':' & SettlementAmount As TransactionKey

FROM
[IncrementalLoadTest\2014-01-02.xls](ooxml, embedded labels, table is Incoming)
WHERE Date(Date,'MM/DD/YYYY') >= '$(vMax)' and Not Exists(TransactionKey);

//----------------------------------------------------till here we got newly added and updated records----------------------------------------

Inner JOIN
LOAD Beneficiary,
    Date,
    OriginalTime,
    Reference,
    Type,
    SettlementAmount,
    TransactionKey

FROM
[IncrementalLoadTest\2014-01-02.xls](ooxml, embedded labels, table is Incoming)

//---------------------------------------------------------this is for deleted records on Live data----------------------------------------------

Re: Re: Re: Re: Re: Incremental load approach

Tue, 23 Sep 2014 09:32:34 GMT

Hi,

script has Misplaced 'load' statement in 'IncrLOad' table.,just change the below script

IncrLOad:
LOAD Beneficiary,
    Date,
    OriginalTime,
    Reference,
    Type,
    SettlementAmount,
    Date & ':' & OriginalTime & ':' & Reference & ':' & SettlementAmount As TransactionKey
FROM
[IncrementalLoadTest\2014-01-02.xls](ooxml, embedded labels, table is Incoming)
WHERE Date(Date,'MM/DD/YYYY') >= '$(vMax)' and Not Exists(TransactionKey);

Regards

Charitha

Re: Re: Re: Re: Re: Re: Incremental load approach

Tue, 23 Sep 2014 09:35:08 GMT

or else you can write like this way

IncrLOad:
LOAD Beneficiary,
    Date,
    OriginalTime,
    Reference,
    Type,
    SettlementAmount;

LOAD *, Date & ':' & OriginalTime & ':' & Reference & ':' & SettlementAmount As TransactionKey

FROM
[IncrementalLoadTest\2014-01-02.xls](ooxml, embedded labels, table is Incoming)
WHERE Date(Date,'MM/DD/YYYY') >= '$(vMax)' and Not Exists(TransactionKey);

Regards

Charitha

Re: Re: Re: Re: Re: Re: Incremental load approach

Tue, 23 Sep 2014 09:41:32 GMT

It still does not recognize the 'composite key', I've tried both suggested solutions, same result

Re: Incremental load approach

Tue, 23 Sep 2014 09:55:05 GMT

Hi,

I saw the QVW and trying to understand Incremental load.

In stage 1 we load all data. when we run again is it will load all data again?

Or do we needs to comment the below code after we save QVD?

InitialOrders:

LOAD OrderID,

     ProductID,

     CustomerID,

     Amount,

     Date_Updated

FROM

[Initial Orders.xlsx]

(ooxml, embedded labels, table is InitialOrders);

store InitialOrders into Orders.qvd(qvd);

drop table InitialOrders;

Re: Re: Re: Re: Re: Re: Incremental load approach

Tue, 23 Sep 2014 10:00:25 GMT

as per your script you can go till STAGE 1 without any script exceptions. as STAGE 1 needs only date field as

conditional field but yes you will have issue in STAGE 2 as not having unique id in Live data souce.

Thanks & Regards

Charitha

Re: Re: Re: Re: Re: Re: Incremental load approach

Tue, 23 Sep 2014 10:04:48 GMT

Hi,

After saving intial load (orders) into Qvd file, need to drop it as it increases the reload time on every reload.

Regards

Charitha

Re: Re: Re: Re: Re: Re: Incremental load approach

Tue, 23 Sep 2014 10:09:09 GMT

and pls mark suggestions Helpful/Correct

Charitha.

Re: Re: Re: Re: Re: Re: Incremental load approach

Tue, 23 Sep 2014 10:23:31 GMT

Hi,

My question is, Do I needs to COMMENT script of initial load after I save my data into QVD?

Thanks.

Re: Re: Re: Re: Re: Re: Incremental load approach

Tue, 23 Sep 2014 10:27:08 GMT

I have done drop formula after my initial load.

here is my example:

INITIAL LOAD

InitialOrders:

LOAD OrderID,

ProductID,

CustomerID,

Amount,

Date_Updated

FROM

[Initial Orders.xlsx]

(ooxml, embedded labels, table is InitialOrders);

STORING INITIAL ORDERS INTO QVD

store InitialOrders into Orders.qvd(qvd);

DROP FORMULA

drop table InitialOrders;

here after pulling off orders into QVD i am doing drop table of Initial orders.

Hope you understand

Thanks

Charitha