Where not Exists() clause with resident load

[cpalbrecht]

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?

[bramkn]

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.

[sunny_talwar · Accepted solution]

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]

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. 😕

[sunny_talwar]

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]

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

[sunny_talwar]

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;

In your code, is this testtable or testtable2?

[cpalbrecht]

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]

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]

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.