overlapping records

[#1]

Hi,

I have some files with data from different periods and I have to load them into 1 QVW.

My problem is that there is some overlapping data in my files (the last records of the previous file are the same of the first records of the next file).

Now i need to find a method to not load these records again and prevent getting double data in my QVW.

The best thing would be to have only one load statement, because all my files have the same name, but with a different timestamp at the end of the filename, so i can load from filename*.csv

Because in the future there will be more files added, and when i use the wildcard in the filename I wouldn't have to edit my script all the time.

Thanks in advance!

[tresesco · Accepted solution]

you can try with DISTINCT load OR an NOT Exists() in the where clause.

Regards, tresesco

[#3]

Hi,

If you have a KeyField you can try something like that :

for each File in filelist ('*.csv')

LOAD *
FROM $(File)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
WHERE NOT EXISTS(KeyField);

next

[#4]

Hi,

Other method is also to write out to a QVD the max record (e.g. max(KeyField)) from the original load, then in the 2nd load, load this value into a variable (e.g. vLastLoadedRec) and restrict the load with a where clause such as where KeyField > $(vLastLoadedRec).

Renaud's technique is also BP.

[#5]

I agree, a LOAD DISTINCT would surely be the best way to handle this