Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
How to write MINUS operator while loading data from Qvd or Excel.
SELECT Column1, Column 2, ... Column _n
FROM tables
[WHERE conditions]
MINUS
SELECT Column 1, Column 2, ... e Column _n
FROM tables
[WHERE conditions];
LOAD Column1,
Column 2,
Column 3,
Column 4,
Column 5
FROM
(
LOAD
Column1,
Column 2,
Column 3,
Column 4,
Column 5
FROM
(
);
Regards,
Krishna
For SQL the equivalent ist EXCEPT
For QlikView the equivalent ist Keep
have a look at this thread for more Details
Understanding Join, Keep and Concatenate
regards
tim
Hi Tim,
How to use EXCEPT.
LOAD Column1,
Column 2,
Column 3,
Column 4,
Column 5
FROM
(
LOAD
Column1,
Column 2,
Column 3,
Column 4,
Column 5
FROM
(
);
Regards,
Krishna
in your case you Need to use the Keep Statement
LOAD Column1,
Column 2,
Column 3,
Column 4,
Column 5
FROM
left keep
LOAD
Column1,
Column 2,
Column 3,
Column 4,
Column 5
FROM
**Note that the two tables are not joined and will be stored in QlikView as two separately named tables.
To exclude the Table B data:
TableB:
LOAD Column1 & '|' & Column2 & '|' & Column3 & '|' & Column4 & '|' & Column5 as Key
FROM
TableA:
LOAD Column1,
Column2,
Column3,
Column4,
Column5
FROM
Where Not(Exists(Key, Column1 & '|' & Column2 & '|' & Column3 & '|' & Column4 & '|' & Column5));
DROP TABLE TableB;
Modify the Key value according to your requirements, The example makes a key of all fields.
Essentially a variation of what Jonathan suggested but with a AutoNumberHash256 function...
The "MINUS" / "EXCEPT" logic is in lines 28 to 32:
SET C=Chr(Floor(Rand()*26)+65);
SET D=Chr(Floor(Rand()*3)+65);
B:
LOAD
'B' AS B,
$(C)&$(C)&$(C)&$(C) AS C1,
$(D) AS C2,
Ceil(Rand()*4) AS C3
AUTOGENERATE 1000000;
STORE B INTO B.QVD (qvd);
DROP TABLE B;
A:
LOAD
'A' AS A,
$(C)&$(C)&$(C)&$(C) AS C1,
$(D) AS C2,
Ceil(Rand()*4) AS C3
AUTOGENERATE 1000000;
STORE A INTO A.QVD (qvd);
DROP TABLE A;
B: LOAD AutoNumberHash256(C1,C2,C3) AS H FROM B.QVD (qvd);A: NOCONCATENATE LOAD * FROM A.QVD (qvd)
WHERE
Not( Exists(H,AutoNumberHash256(C1,C2,C3)) );
DROP TABLE B;