topic Concatenate no duplicates in QlikView

Concatenate no duplicates

Fri, 24 Sep 2010 10:20:15 GMT

Hi All,

I am trying to concatenate 2 tables - the first is a "live" table reloaded every 15 minutes and the second an archive table created weekly.

I want all values from the live table and only those rows from archive that are not duplicates. I have used:

where not exists(accountid)

but i need a where not exists values for all fields in a row as the accountid will re-occur multiple times on different dates- any ideas?

Many thanks

Mike

Concatenate no duplicates

Anonymous — Fri, 24 Sep 2010 13:39:27 GMT

Mike,

Here are two options that I can think of now:

1. Concatenate all records first. After this, run LOAD DISTNCT ... RESIDENT to eliminate duplicates.

2. Create group key that includes all fields (or at least all fields which are enough for uniqueness):
autonumberhush128(field1 & field2 & ...) as Key
And then use the same not exists(Key)

(There should be more effective ways, I guess)

Concatenate no duplicates

Fri, 24 Sep 2010 14:56:57 GMT

Thanks, I am currently using a concatenated key to distinguish rows (which works) but it seems strange there is not a less round about and more efficient way to do this..

Mike