Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to write MINUS operator while loading data from Qvd or Excel.

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

(
qvd);


LOAD
     Column1,
   
Column 2,
   
Column 3,
         Column 4,
          Column 5

  FROM



(
qvd

);

Minus.png


Regards,

Krishna

5 Replies
zhadrakas
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

Hi Tim,

How to use EXCEPT.

LOAD Column1,
    
Column 2,
    
Column 3,
         Column 4,
          Column 5
FROM

(
qvd);


LOAD
     Column1,
   
Column 2,
   
Column 3,
         Column 4,
          Column 5

  FROM



(
qvd

);



Regards,

Krishna

zhadrakas
Specialist II
Specialist II

in your case you Need to use the Keep Statement

LOAD Column1,
   
Column 2,
   
Column 3,
        Column 4,
          Column 5
FROM (qvd);

left keep
LOAD
    Column1,
   
Column 2,
   
Column 3,
        Column 4,
          Column 5

  FROM (qvd);

**Note that the two tables are not joined and will be stored in QlikView as two separately named tables.

jonathandienst
Partner - Champion III
Partner - Champion III

To exclude the Table B data:


TableB:

LOAD Column1 & '|' & Column2 & '|' & Column3 & '|' & Column4 & '|' & Column5 as Key

FROM (qvd);

TableA:

LOAD Column1,

  Column2,

  Column3,

  Column4,

  Column5

FROM (qvd)

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
petter
Partner - Champion III
Partner - Champion III

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;