Check if a field value exists within a specific table?

[Anonymous]

I'm doing incremental loads and as part of that I have the usual concatenate from my qvd where Not Exists(MyField).

This works fine when the field only exists in one table, but I think it's failing when I have the field in multiple tables.

I have a facts table that among other things has a CallKey field which is unique to a call. This table has joined data so the CallKey will appear in multiple rows.

For this reason I also have a table CallCount which has fields CallKey and Count which has one row per CallKey and a 1 in the Count field.

My problem is that I'm saving both of these tables to QVDs then trying to load new/updated values afterwards.

I first load old data for Facts, which includes my CallKey values, then I load old data for CallCount, but because I only want to load records not already loaded I do a where Not Exists(CallKey).

The trouble is, the CallKey does already exist, but in another table.

How can I still load the record in my CallCount table, where the Key field already exists in another table?

[Anonymous]

Forgot to mention, I know I could simply take the CallCount out of the incremental loading part and calculate it once I built my Facts table completely, however the problem with that is that My facts table is very large and calculating the CallCount table from scratch ever time seems very inefficient.

[swuehl · Accepted solution]

You can keep an alias of your key field in your CallCount Table and use exists() with 2 arguments, something like

CONCATENATE (KeyCount) LOAD

     Key,

     Key as Alias,

     1 as Count

FROM QVD.qvd where not exists(Alias, Key);

[Anonymous]

Not sure I understand correctly.

Would I need to add the alias in when I create the table from the database too?

As far as I can tell that will look for the value in the field Key, in the field Alias, but the KeyCount table won't have that Alias field unless I create it when I load from the DB first.

[swuehl]

Right (if I understand you correctly), you would need to create the Alias also when you create the KeyCount table.

It's just a field that only exists in one table, containing the values you want to check (not 100% sure about your requirements, so maybe this is not what you really need).

exists() with two parameters allows to check your input field Key value (or an expression) against this different field.

[Anonymous]

Great, thanks for the extra explanation.