Drop record from a table

Tue, 06 Apr 2010 12:59:59 GMT

Hi guys,

Which is the fast way to drop record from a table, starting from an "exclusion table".

I have let's say 'table1' of all my record, but I've to exclude those record that I've in 'ExclusionTable'

Matching field are 2. So which is in your opinion the fastest way to exclude record of 'Exclusion Table' from 'Table1'

Thanks

Sergio

Drop record from a table

johnw — Wed, 07 Apr 2010 22:00:26 GMT

The fastest way is probably to not load the excluded rows in the first place:

[Exclusion Table]:
LOAD Field1 & ',' & Field2 as ExclusionKey
...
;
[Main Table]
LOAD
UniqueID
,some fields
,Field1
,Field2
,some other fields
...
WHERE NOT EXISTS (ExclusionKey,Field1 & ',' & Field2)
;

If you MUST load the main table first (or if using QVDs, where the not exists above would, I believe, cause an unoptimized load), probably something like this:

[Main Table]
LOAD
UniqueID
,some fields
,Field1
,Field2
,some other fields
...
;
[Exclusion Table]:
LOAD Field1 & ',' & Field2 as ExclusionKey
...
;
INNER JOIN ([Main Table])
LOAD UniqueID
RESIDENT [Main Table]
WHERE NOT EXISTS (ExclusionKey,Field1 & ',' & Field2)
;

topic Drop record from a table in QlikView

Drop record from a table

Drop record from a table