Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Which where clause, there are two where clauses in your script?
The second one: Where not Exists([%SessionIdDimKey], [%SessionIdDimKey_Tmp]);
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?
Yes, I have a big fact table:
[FactTable]:
NoConcatenate
Load
.... as ...,
.... as ...,
.... as [%SessionIdDimKey],
.... as ...
Resident [...];
Is this possibly loading all the values which you might be adding here?
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.
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
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!