Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Justin
Have you looked at using:
Where not exists...........
Best Regards, Bill
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.
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;
Thanks all for your speedy replies, I will test this out now!