Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Concatenate Load Where New ID (Not Exists)

Hi all,

I want to concatenate two tables. Table A will be the main table; Table B data should only be loaded if a unique ID field in the table does not exist in Table A. For example, see pseudo-code below:

Table A:

Load

     UniqueField,

     Field1,

     Field3

From

SomeTable;

Table B:

Concatenate(Table A)

Load

     UniqueField

     Field1,

     Field4

From

OtherTable

WHERE does not exist(UniqueField); //or where "Is New" UniqueField

Is there an easy way to do this?

1 Solution

Accepted Solutions
simenkg
Specialist
Specialist

You need to first load the field you want to check as a separate field and use the syntax below.

Table A:

Load

     UniqueField,

     UniqueField as UniqueTestField,

     Field1,

     Field3

From

SomeTable;

Table B:

Concatenate(Table A)

Load

     UniqueField

     Field1,

     Field4

From

OtherTable

WHERE not exists(UniqueTestField,UniqueField);

View solution in original post

7 Replies
JonnyPoole
Employee
Employee

Have you tried the 'NOT' keryword with the Exists() function ?  Note Exists is plural

Table A:

Load

     UniqueField,

     Field1,

     Field3

From

SomeTable;

Table B:

Concatenate(Table A)

Load

     UniqueField

     Field1,

     Field4

From

OtherTable

WHERE NOT (exists(UniqueField) ) ;

Not applicable
Author

Will the Not Exists() keyword work the way I want, or will it only load where the UniqueFied is not NULL in "OtherTable"?

simenkg
Specialist
Specialist

You need to first load the field you want to check as a separate field and use the syntax below.

Table A:

Load

     UniqueField,

     UniqueField as UniqueTestField,

     Field1,

     Field3

From

SomeTable;

Table B:

Concatenate(Table A)

Load

     UniqueField

     Field1,

     Field4

From

OtherTable

WHERE not exists(UniqueTestField,UniqueField);

jeckstein
Partner - Creator
Partner - Creator

I know this is an old post but is there any chance someone could explain why we needed to create this "UniqueTestField" and why you could not just us "UniqueField"

simenkg
Specialist
Specialist

Hi, James.

Because UniqueField exists in both tables, and as such all values in Table B exists in the global field UniqueField and you will not get any rows loaded from table B. UniqueTestField in this case only exists for the data in Table A and the test therefore correctly tests for instances that exists in table A.

Regards

SKG

PrashantSangle

I don't think so

where not exist(uniqueField,uniqueField) must work

can you share any example which demonstrate example.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
simenkg
Specialist
Specialist

Okay.

So there are two different scenarios and two different problems you can have with using the original field in the not exists().

Scenario 1: Reading from a table in memory.

Table A:

load * form fileA.qvd

Table B:

load * from fileB.qvd

Concatenate(Table A)

load * resident Table B where not exists(UniqueField,UniqueField);

In this case all the values of UniqueField in TableB already exists in memory.

When QlikView stores a field in memory it really stores all the unique values in one table and in the data-table it stores the index of the value in the value table. So when we do a check for exists we check if the value exists in the list of unique values. Since TableB is already in memory all of the values in UniqueField already exists in the value table.

Scenario 2:

Reading from a file with multiple lines per "uniqueField":

TableA:

load * inline [

UniqueField, Data

1,TableA

2,TableA

3,TableA

4,TableA

5,TableA

];

Concatenate(TableA)

TableB:

load * inline [

UniqueField, Data

4,TableB

5,TableB

6,TableB

7,TableB

8,TableB

8,SomeThingElse

]

where (not exists(UniqueField,UniqueField));

the result will be:

UniqueField Data
1TableA
2TableA
3TableA
4TableA
5TableA
6TableB
7TableB
8TableB

When we read the first row with UniqueField = 8, then UniqueField = 8 will exist in the internal value table and therefore the WHERE clause will fail for the next fow with UniqueField = 8;

Using a different field for testing solves both these problems as ONLY the values that already existed in TableA exists in this test field.

See the attached qvw