Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Incremental load with multiple dates

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

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

5 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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/2009110
1/2/2009120
1/2/2009121
1/2/2009122
1/2/2009123
1/2/2009124
1/2/2009125
1/2/2009126
1/2/2009127
1/2/2009128
1/2/2009129


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

1/1/2009110
1/2/2009120
1/3/2009130
1/3/2009131
1/3/2009132
1/3/2009133
1/3/2009134
1/3/2009135
1/3/2009136
1/3/2009137
1/3/2009138
1/3/2009139


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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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

Thanks,
Dino

Not applicable
Author

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