Concatenate Load Where New ID (Not Exists)

[#1]

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?

[JonnyPoole]

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) ) ;

[#3]

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

[simenkg · Accepted solution]

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]

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]

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,

[simenkg]

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
1	TableA
2	TableA
3	TableA
4	TableA
5	TableA
6	TableB
7	TableB
8	TableB

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