topic Where not exists in QlikView

Where not exists

Thu, 12 Jan 2012 21:38:07 GMT

I created a table, Table1 with 1 field in it, called Field1.

I then pull data from another source, table2 that contains the the same data in field one of table1 but also has additional data.I want to add the data from table to, to table1, where it does not already exist in table1. I am having trouble using the where not exist. My script looks like this,

inner join (table1)
Load
field1 Resident table2 where not(Exists(%KEY_SDMATNR));

Where not exists

Thu, 12 Jan 2012 22:51:01 GMT

hello,

i believe what you really need is to concatenate both sets of data.

try this:

table1:

LOAD field1 FROM...

table2:

LOAD field1 as field2 FROM...

Concatenate

LOAD field1 as field2

Resident table1

Where not Exists(field2, field1);

You'll get in table2 the full set of values.

Hope this helps.

Where not exists

Fri, 13 Jan 2012 14:51:01 GMT

I keep getting this error: Table not found
Concatenate
LOAD
%KEY_SDMATNR as %KEY_SDMATNR1
Resident BillingDocs
Where not Exists(%KEY_SDMATNR1, %KEY_SDMATNR); My script is

BillingDocs1:

Load Distinct
%KEY_SDMATNR
Resident PASD;

BillingDocs2:
Load Distinct
%KEY_SDMATNR as %KEY_SDMATNR1
Resident KNOP;

Concatenate
LOAD
%KEY_SDMATNR as %KEY_SDMATNR1
Resident BillingDocs
Where not Exists(%KEY_SDMATNR1, %KEY_SDMATNR);

Thoughts?

Where not exists

Fri, 13 Jan 2012 15:08:29 GMT

In the last part, try to rename BillingDocs to BillingDocs1 as it is declared in the beginning of your script.

Where not exists

Fri, 13 Jan 2012 17:22:32 GMT

Thanks I figured out the issue, but based on my previous code I now need to concatenate table1 with 4 fields and table 2 with 4 fields and I have yet to be sucessful. The fields all called the same in each table, and I want to add the records from table 2 to table 1 that are not already in table 1. I have tried a couple of things and I'm just missing something. Can you send me the script example? Thanks again, Sean

Where not exists

Fri, 13 Jan 2012 20:07:08 GMT

Well in that case it seems that 4 fields constitute a unique record instead of 1. We can still proceed with the same logic as before, but this time using in addition a new field formed of those 4 fields as shown below:

QUALIFY *;

table2:
LOAD Field1 & '|' & Field2 & '|' & Field3 & '|' & Field4 as KeyField,
Field1,
Field2,
Field3,
Field4
FROM ...
;

table1:
LOAD Field1 & '|' & Field2 & '|' & Field3 & '|' & Field4 as KeyField,
Field1,
Field2,
Field3,
Field4
FROM ...
;

UNQUALIFY *;

Concatenate
LOAD table2.KeyField as table1.KeyField,
table2.Field1 as table1.Field1,
table2.Field2 as table1.Field2,
table2.Field3 as table1.Field3,
table2.Field4 as table1.Field4
Resident table2
Where not Exists(table1.KeyField,table2.KeyField);

In the above code I used the Qualify/Unqualify in order to keep both of table2 and table1 (updated) in the tableviewer without creating synthetic keys since the fields in both tables have same names (you can also rename each field in one of the two tables instead of using the qualify/unqualify and then in the concatenate part you adjust the field names appropriately, it's up to you).

If you want instead to drop table2 in the end since table1 will contain all needed records then you can use the code below:

table2:

LOAD Field1 & '|' & Field2 & '|' & Field3 & '|' & Field4 as KeyField2,
Field1,
Field2,
Field3,
Field4
FROM ...
;

table1:
LOAD Field1 & '|' & Field2 & '|' & Field3 & '|' & Field4 as KeyField,
Field1,
Field2,
Field3,
Field4
FROM ...
;

Concatenate
LOAD KeyField2 as KeyField,
Field1,
Field2,
Field3,
Field4
Resident table2
Where not Exists(KeyField, KeyField2);

DROP Table table2;

Hope this helps you Sean.

Where not exists

Fri, 13 Jan 2012 20:49:41 GMT

YES! Thank you very much!!