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?

[sunny_talwar]

Which where clause, there are two where clauses in your script?

[cpalbrecht]

The second one: Where not Exists([%SessionIdDimKey], [%SessionIdDimKey_Tmp]);

[sunny_talwar]

Is this the beginning of the script or do you have script before what you have given? If there is script before this, do you happen to have [%SessionIdDimKey] field referred anywhere before also?

[cpalbrecht]

Yes, I have a big fact table:

[FactTable]:

NoConcatenate

Load

     .... as ...,

     .... as ...,

     .... as [%SessionIdDimKey],

     .... as ...

Resident [...];

[sunny_talwar]

Is this possibly loading all the values which you might be adding here?

[cpalbrecht]

What I am doing is the following:

I make a REST Call to get new data. Then I have to do some modifications and so on.

I split the new data into one fact table and multiple dimension tables. The fact table is "FactTable". This table contains all dimension keys. And one key is "[%SessionIdDimKey]".

So that key is contained in fact table and in the new "$(vTableName)".

I need to load the data for that table from "TmpDataJoinTable". From that table I load 3 columns into  "$(vTableName)".

When no qvd exists, I create one and store the data from "$(vTableName)" into that new qvd.

The next time I run the data load script I want to take the new data from the actual run and concatenate that data with the data stored in the qvd.

[sunny_talwar]

I understand what you are trying to do, but I am not sure what is not right. All I am trying to explain is that Where not Exists([%SessionIdDimKey], [%SessionIdDimKey_Tmp]); will exclude all possible values within %SessionIdDimKey. If ABC appears in %SessionIdDimKey anywhere before this where not exists, it will be excluded from the concatenate

[cpalbrecht]

Ah, now I understand. If the same column exists in a complete different table and the specific value is stored there, I can't load it into another table with "Where not Exists".

I didn't know, that "Where not Exists" applies globally. I thought its only validating the table where I want to load the value.

Thank you so much!