Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cpalbrecht
Contributor

Where not Exists() clause with resident load

I have for example two tables:

test1:

NoConcatenate

LOAD * INLINE [

F1, F2

a, 210

];


test2:

NoConcatenate

LOAD * INLINE [

F1, F2

a, 212

b, 422

f, 424

h, 24

h, 242

g, 561

g, 562

g, 563

];


Now I would like to concatenate the two tables, but with the "Where not Exists" clause.


Concatenate(test1)

Load *

Resident test2

Where not Exists(F1);


Drop Table test2;


The expected result should be:


test1:

F1, F2

a, 210

b, 422

f, 424

h, 24

g, 561

But the the result now is:

test1:

F1, F2

a, 210

When I am doing it like this, it is working as expected:

test1:

LOAD * INLINE [

F1, F2

a, 210

];


LOAD * INLINE [

F1, F2

a, 212

b, 422

f, 424

h, 24

h, 242

g, 561

g, 562

g, 563

]

Where not Exists(F1);


What is wrong with my first version? What is the difference? And how can I use two resident tables to get the expected result?

1 Solution

Accepted Solutions
MVP
MVP

Re: Where not Exists() clause with resident load

The problem is that by the time you do the load with concatenate, the values for F1 already include all the data from table F1. But in the case it is working, F1 only includes a from test1. Does that make sense? To use resident... you can try this

test1:

NoConcatenate

LOAD * INLINE [

F1, F2

a, 210

];


test2:

NoConcatenate

LOAD F1 as F1_Temp,

     F2;

LOAD * INLINE [

F1, F2

a, 212

b, 422

f, 424

h, 24

h, 242

g, 561

g, 562

g, 563

];


Concatenate(test1)

Load F1_Temp as F1,

     F2

Resident test2

Where not Exists(F1, F1_Temp);


Drop Table test2;

First you rename F1 to call it F1_Temp and this was F1 will only still have one value (a) and then check F1_Temp against F1 in your where exist statement

17 Replies
b_knuever
Contributor III

Re: Where not Exists() clause with resident load

Because you have already loaded the data. it actually exists already. Try using a dummy field in the first table and concat the second table with where not exists on that.

MVP
MVP

Re: Where not Exists() clause with resident load

The problem is that by the time you do the load with concatenate, the values for F1 already include all the data from table F1. But in the case it is working, F1 only includes a from test1. Does that make sense? To use resident... you can try this

test1:

NoConcatenate

LOAD * INLINE [

F1, F2

a, 210

];


test2:

NoConcatenate

LOAD F1 as F1_Temp,

     F2;

LOAD * INLINE [

F1, F2

a, 212

b, 422

f, 424

h, 24

h, 242

g, 561

g, 562

g, 563

];


Concatenate(test1)

Load F1_Temp as F1,

     F2

Resident test2

Where not Exists(F1, F1_Temp);


Drop Table test2;

First you rename F1 to call it F1_Temp and this was F1 will only still have one value (a) and then check F1_Temp against F1 in your where exist statement

cpalbrecht
Contributor

Re: Where not Exists() clause with resident load

Your example is working great! Thanks.

But it is not working, if test2 table is not an inline load but a data load from qvd file. :/

MVP
MVP

Re: Where not Exists() clause with resident load

You mean something like this doesn't work?

test1:

NoConcatenate

LOAD * INLINE [

F1, F2

a, 210

];

test2:

NoConcatenate

LOAD F1 as F1_Temp,

    F2

FROM XYZ.qvd (qvd);

Concatenate(test1)

Load F1_Temp as F1,

    F2

Resident test2

Where not Exists(F1, F1_Temp);

Drop Table test2;

cpalbrecht
Contributor

Re: Where not Exists() clause with resident load

I have a simulation for incremental load. For the first run, I load the data into a table and after that I staore the table in a QVD file. For the next run, I load another data and concatenate the loaded data with the qvd data.

One difference is, that the data of the testtable2 is not an inline load. It is from another resident table (testtable1).

if (IsNull(QvdCreateTime('lib://Data/testtable'))) then


testtable1:

NoConcatenate

LOAD * INLINE [

    F1, F2

    a, 212

    b, 422

    f, 424

    h, 25

    g, 561

];


testtable2:

NoConcatenate

Load *

Resident testtable1;


else


testtable1:

NoConcatenate

LOAD * INLINE [

    F1, F2

    a, 210

    z, 600

];


testtable2:

NoConcatenate

Load *

Resident testtable1;


end if


if not(IsNull(QvdCreateTime('lib://Data/testtable2'))) then

 

  testtable2_Tmp:

  NoConcatenate

  Load

  F1 as F1_Tmp,

    F2

  FROM 'lib://Data/testtable2' (qvd);

 

  Concatenate(testtable2)   

  Load

  F1_Tmp as F1,

    F2

  Resident testtable2_Tmp

  Where not Exists(F1, F1_Tmp);

 

  Drop Table testtable2_Tmp;

end if


Store testtable2 into 'lib://Data/testtable2' (qvd);


Drop Table testtable1;


You have to run the example two times. After the second run the result table should be:


testtable2:

F1, F2

a, 210

z, 600

b, 422

f, 424

h, 25

g, 561

MVP
MVP

Re: Where not Exists() clause with resident load

I ran the same exact thing in QlikView...and it seemed to have worked for me (with just one change)

IF (IsNull(QvdCreateTime('testtable2.qvd'))) then


testtable1:

NoConcatenate

LOAD * INLINE [

     F1, F2

     a, 212

     b, 422

     f, 424

     h, 25

     g, 561

];

testtable2:

NoConcatenate

Load *

Resident testtable1;


ELSE


testtable1:

NoConcatenate

LOAD * INLINE [

     F1, F2

     a, 210

     z, 600

];


testtable2:

NoConcatenate

Load *

Resident testtable1;


ENDIF


IF not(IsNull(QvdCreateTime('testtable2.qvd'))) then


testtable2_Tmp:

NoConcatenate

LOAD F1 as F1_Tmp,

F2

FROM 'testtable2.qvd' (qvd);

Concatenate(testtable2)  

LOAD F1_Tmp as F1,

F2

Resident testtable2_Tmp

Where not Exists(F1, F1_Tmp);


Drop Table testtable2_Tmp;


ENDIF


STORE testtable2 into 'testtable2.qvd' (qvd);

DROP Table testtable1;

Capture.PNG

In your code, is this testtable or testtable2?

Capture.PNG

cpalbrecht
Contributor

Re: Where not Exists() clause with resident load

Oh, that was the mistake in my example.

But my real world program (similar script with other data) is still not running. I have to check the code again (after checking it already 1000 times). But now I know it should work.

cpalbrecht
Contributor

Re: Where not Exists() clause with resident load

But this is also working:

if (IsNull(QvdCreateTime('lib://Data/testtable2'))) then


testtable1:

NoConcatenate

LOAD * INLINE [

    F1, F2

    a, 212

    b, 422

    f, 424

    h, 25

    g, 561

];


testtable2:

NoConcatenate

Load

F1,

F2

Resident testtable1;


else


testtable1:

NoConcatenate

LOAD * INLINE [

    F1, F2

];


testtable2:

NoConcatenate

Load

F1,

F2

Resident testtable1;


end if


if not(IsNull(QvdCreateTime('lib://Data/testtable2'))) then

 

  Concatenate(testtable2)

  Load

  F1,

    F2

  FROM 'lib://Data/testtable2' (qvd)

  Where not Exists(F1);

 

end if


Store testtable2 into 'lib://Data/testtable2' (qvd);


Drop Table testtable1;

cpalbrecht
Contributor

Re: Where not Exists() clause with resident load

So I cannot find any difference to my previous posted examples. This is my original code:

$(vTableName):

NoConcatenate

Load Distinct

    X1 & '|' & X2 & '|' & X3 as [%SessionIdDimKey],

    X1 & '|' & X2 & '|' & X3 as [Unique Session Id],

    X4 as [Session Number]           

Resident [TmpDataJoinTable]

Where not IsNull(ocpp_session_id);


// store and join data in/from qvd file

let vQvdPath = vDataPathPrefix & '$(vTableName).qvd';


if not(IsNull(QvdCreateTime(vQvdPath))) then

 

  $(vTableName)_Tmp:

  NoConcatenate

  Load

  [%SessionIdDimKey] as [%SessionIdDimKey_Tmp],

    [Unique Session Id],

    [Session Number] 

  FROM [$(vQvdPath)] (qvd);

 

  Concatenate($(vTableName))   

  Load

  [%SessionIdDimKey_Tmp] as [%SessionIdDimKey],

    [Unique Session Id],

    [Session Number] 

  Resident $(vTableName)_Tmp

  Where not Exists([%SessionIdDimKey], [%SessionIdDimKey_Tmp]);

 

  Drop Table $(vTableName)_Tmp;

end if


Store [$(vTableName)] into [$(vQvdPath)] (qvd);


When I remove the where clause, it is working. With where clause not.