Script equivalent to SQL minus

[justin_morley]

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:

ID

1

2

and

ID

1

2

3

I want a command which returns just:

ID

3

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

[rbecher]

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

[Anonymous]

Justin

Have you looked at using:

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

Best Regards,     Bill

[salto]

Hello, the exists function can help:

Data1:

LOAD * INLINE [

    ID1

    1

    2

];

Data2:

LOAD * INLINE [

    ID2

    1

    2

  3

];

Data:

noconcatenate

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

drop table Data1;

drop table Data2;

Hope this helps.

[dmohanty]

Justin,

This will help:

A:

load * Inline
[
ID
1
2
];

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

Drop Table A;

[justin_morley]

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