Qlik Community

Ask a Question

App Development

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

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
krishna4bo
Creator
Creator

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

krishna4bo
Creator
Creator
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

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

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;