Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

krishna4bo
Contributor

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

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

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

krishna4bo
Contributor

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

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

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

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.

MVP
MVP

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

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
MVP
MVP

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

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;

Community Browser