Where not exists - incremental loading

[pgdavis2]

When doing an incremental load, we pull in new data for a set of units that has a date with it.

The historical qvd file is then loaded where not exists primary.

That works ok except that we are sometimes pulling in units from the historical qvd for a particular date that don't exist in the new data.

We only want to pull in data from the past that does not match the date of the new data.  We can't seem to do that since the date is not unique field.

I have

//Data:     //this creates the historical data, which is the forecast edition

//LOAD Edition,

//     Date,

//     Hour,

//     Name,

//     Value,

//          Date&Hour&Name as Primarykey

//FROM

//.\Forecast.csv

//(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//

//STORE * from Data into historical.qvd (qvd);

//This loads the new data from Actual.csv

Data:

LOAD Edition,

     Date,

     Hour,

     Name,

     Value,

          Date&Hour&Name as Primarykey

FROM

.\Actual.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

concatenate Load * from historical.qvd (qvd) where not exists(Primarykey,Primarykey);

Unit 3 was in forecast edition for 5/7, but not in actual edition (new data)

I don't want to pull it back from the QVD -  I only want 5/8 data to be pulled back from the QVD

Help!

[#2 · Accepted solution]

Hello,

take a look at my sunday afternoon suggestion: I assumed that you only want to read in from qvd data of those units that come in via actual.csv. Search for //RK to see what I have done in the script.

If the filter criteria is for ex. the date, so exchange RefUnit with RefDate.

HtH

Roland

[pgdavis2]

This is very helpful!  I believe it solves the problem or at least points me to the solution.

The list of units in Actual edition can change daily, and may or may not be the same as the list of units that was forecast for that particular day.

But in the end, when we load an Actual edition for a particular day, we don't want to pull back anything at all labeled as Forecast edition for that date.

I will play with this some and see how it works under various scenarios.

Thanks for taking the time Roland

[pgdavis2]

I think RefDate, as you suggested, is what we needed...

Thanks!

[#5]

Yes,

This points into the right direction.

glad to help you, Roland