topic Re: How to write MINUS operator while loading data from Qvd or Excel. in App Development

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

Anonymous — Mon, 15 Jan 2018 05:57:41 GMT

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

);

Regards,

Krishna

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

zhadrakas — Mon, 15 Jan 2018 06:41:21 GMT

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

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

Anonymous — Mon, 15 Jan 2018 06:48:50 GMT

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

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

zhadrakas — Mon, 15 Jan 2018 06:54:57 GMT

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.

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

jonathandienst — Mon, 15 Jan 2018 07:13:11 GMT

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.

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

petter — Mon, 15 Jan 2018 07:42:07 GMT

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;