topic overlapping records in QlikView

overlapping records

Tue, 29 Mar 2011 12:22:33 GMT

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!

overlapping records

tresB — Tue, 29 Mar 2011 12:56:04 GMT

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

Regards, tresesco

overlapping records

Tue, 29 Mar 2011 12:57:59 GMT

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

overlapping records

Tue, 29 Mar 2011 13:02:28 GMT

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.

overlapping records

Tue, 29 Mar 2011 13:10:16 GMT

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