topic Re: Script equivalent to SQL minus in QlikView

Script equivalent to SQL minus

justin_morley — Tue, 19 Nov 2013 10:00:36 GMT

Hi all,

Is there a QlikView script equivalent to the SQL minus command?

Here is a definition I just pulled from the interwebs: "The SQL MINUS query returns all rows in the first SQL SELECT statement that are not returned in the second SQL SELECT statement."

I have two data sets which look like this:

and

I want a command which returns just:

I know I can write script to compare the datasets, but I was wondering if there was a native method?

This is similar to incremental load in many respects, but I've got to do an expensive service call with each new record

Thanks in advance,

Justin

Re: Script equivalent to SQL minus

rbecher — Tue, 19 Nov 2013 10:06:52 GMT

Hi Justin,

not really. But you can use NOT EXISTS() in the second load and then drop the first table:

FirstTab:
LOAD * INLINE [
    ID
    1
    2
];

SecondTab:
NOCONCATENATE LOAD * Where Not Exists(ID);
LOAD * INLINE [
    ID
    1
    2
    3
];

Drop Table FirstTab;

But, it getting a bit more complex with several fields..

- Ralf

Re: Script equivalent to SQL minus

Anonymous — Tue, 19 Nov 2013 10:08:15 GMT

Justin

Have you looked at using:

Where not exists...........

Best Regards, Bill

Re: Script equivalent to SQL minus

salto — Tue, 19 Nov 2013 10:13:34 GMT

Hello, the exists function can help:

Data1:

LOAD * INLINE [

ID1

];

Data2:

LOAD * INLINE [

ID2

];

Data:

noconcatenate

load * resident Data2 where not exists(ID1,ID2);

drop table Data1;

drop table Data2;

Hope this helps.

Re: Script equivalent to SQL minus

dmohanty — Tue, 19 Nov 2013 10:19:29 GMT

Justin,

This will help:

load * Inline
[
ID
1
2
];

B:
NoConcatenate
load * Inline
[
ID
1
2
3
]
where not Exists (ID)
;

Drop Table A;

Re: Script equivalent to SQL minus

justin_morley — Tue, 19 Nov 2013 10:24:39 GMT

Thanks all for your speedy replies, I will test this out now!