Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Where not exists

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));
6 Replies
Not applicable

Where not exists

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.

Not applicable

Where not exists

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?

Not applicable

Where not exists

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

Not applicable

Where not exists

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

Not applicable

Where not exists

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.
Not applicable

Where not exists

YES!  Thank you very much!!

Community Browser