topic Incremental load with multiple dates in QlikView

Incremental load with multiple dates

Wed, 29 Apr 2009 22:24:25 GMT

I am currently doing an incremental load of historical information by loadig a QVD with the prior data, appending the current day's data, then rewriting the QVD. It looks something like this:

Read the date from the first record of the new file and store it in a variable:
FindDate:
LOAD InvDate AS BillingInfoFileDate
FROM BillingInfo.txt (ansi, txt, delimiter is ';', embedded labels, msq)
WHERE RecNo()=1;
LET vBillingInfoFileDate= num(peek('BillingInfoFileDate', 0));
Load the existing history from the QVD, excluding the date in the new file (I do this to allow for repeated loads throughout the day. It also allows me to correct a previous load of incorrect data):
BillingHistory:
LOAD *
FROM BillingHistory.qvd (qvd)
WHERE InvDate <> $(vBillingInfoFileDate);
Add the records from the new file and rewrite the QVD:
CONCATENATE (BillingHistory)
LOAD *
FROM BillingInfo.txt (ansi, txt, delimiter is ';', embedded labels);
STORE * FROM BillingHistory INTO BillingHistory.qvd;

This method works great right now because I know that the new BillingInfo file will always contain only one date's worth of information. That's about to change because I have to start picking up activity over the weekend and the job that runs Monday morning will cover up to three days. I need a way to read the new file, get all dates included in that file and exclude those dates from the QVD load to prevent loading duplicate records. Individual daily loads on Sat and Sun are not possible.

Thanks in advance for any ideas on how I might approach this.
Dino

Incremental load with multiple dates

rwunderlich — Wed, 29 Apr 2009 23:06:18 GMT

Does every row of the txt file have a InvDate? If so, you can approach it this way.

1. LOAD all the data from BillingInfo.txt.

2. LOAD * BillingHistory.qvd (qvd) WHERE NOT exists(InvDate)

-Rob

Incremental load with multiple dates

Thu, 30 Apr 2009 00:35:46 GMT

Yes, every row has among other things, Date and Invoice Number. When I try the method above, I end up losing all invoices from the previous dates except one for each date. I mocked up up some data to test and here's what happened:

First Load File:
Date,InvNbr
1/1/2009,110
1/1/2009,111
1/1/2009,112
1/1/2009,113
1/1/2009,114
1/1/2009,115
1/1/2009,116
1/1/2009,117
1/1/2009,118
1/1/2009,119

Loads and writes to QVD as expected (first time around I didn't load the QVD because it didn't exist yet.

The I load the next file:
Date,InvNbr
1/2/2009,120
1/2/2009,121
1/2/2009,122
1/2/2009,123
1/2/2009,124
1/2/2009,125
1/2/2009,126
1/2/2009,127
1/2/2009,128
1/2/2009,129

What seems to happen is that the initial file loads as expected. I load the QVD, which doesn't contain1/2/2009 yet, but only the first entry from 1/1/2009 loads and I end up with this:

1/1/2009	110
1/2/2009	120
1/2/2009	121
1/2/2009	122
1/2/2009	123
1/2/2009	124
1/2/2009	125
1/2/2009	126
1/2/2009	127
1/2/2009	128
1/2/2009	129

Then I load a file for 1/3/2009 and end up with this:

1/1/2009	110
1/2/2009	120
1/3/2009	130
1/3/2009	131
1/3/2009	132
1/3/2009	133
1/3/2009	134
1/3/2009	135
1/3/2009	136
1/3/2009	137
1/3/2009	138
1/3/2009	139

So it seems that the exists() is loading the first invoice of each date from the qvd, then rejecting subsequent records with the same date but different invoice numbers.

Incremental load with multiple dates

rwunderlich — Thu, 30 Apr 2009 00:53:03 GMT

Sorry for my incomplete answer. What you need to do is define a combination of fields that represent a Key and use that in the exists.

Date & '|' & InvNbr as PK

and then

WHERE NOT exists(PK)

If you have large keys, you can reduce the size by using authonumberhash128() -- but then you have to repeat the hash in the QVD concatenate. For a complete Incremental Reload example, including the autonumberhash128 technique, see the QV Cookbook sample "Incremental reload template and example".

You can get the QV Cookbook from:

http://www.robwunderlich.com/Download.html

-Rob

Incremental load with multiple dates

Thu, 30 Apr 2009 01:27:18 GMT

Yes, that seems to do it. My challenges are then to find the right combination of fields to use as the key and to stop users from changing history on me, neither of which I think you'll be able to help with.

Thanks,
Dino

Incremental load with multiple dates

Tue, 20 Jul 2010 07:31:46 GMT

Hi Rob,

Can you please do the required changes in my document so that incremental load(insert,update and delete) works.

Please look into the application that I have attached and do the required changes.

http://community.qlik.com/forums/t/28159.aspx