Incremental load approach

[#1]

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

[#2]

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----------------------------------------------

[#3]

Hi,

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

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);

AS

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

[#4]

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

[#5]

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

[#6]

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;

[#7]

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

[#8]

Hi,

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

Regards

Charitha

[#9]

and pls mark suggestions Helpful/Correct

Charitha.

[#10]

Hi,

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

Thanks.

[#11]

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