Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
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
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.