Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
justin_morley
Contributor

Script equivalent to SQL minus


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

5 Replies
MVP
MVP

Re: Script equivalent to SQL minus

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

Justin

Have you looked at using:

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

Best Regards,     Bill

salto
Valued Contributor II

Re: Script equivalent to SQL minus

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
Valued Contributor

Re: Script equivalent to SQL minus

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
Contributor

Re: Script equivalent to SQL minus

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

Community Browser