topic Concatenate Load Where New ID (Not Exists) in QlikView

Concatenate Load Where New ID (Not Exists)

Fri, 05 Dec 2014 15:51:40 GMT

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?

Re: Concatenate Load Where New ID (Not Exists)

JonnyPoole — Fri, 05 Dec 2014 16:34:14 GMT

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

Re: Concatenate Load Where New ID (Not Exists)

Fri, 05 Dec 2014 16:41:26 GMT

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

Re: Concatenate Load Where New ID (Not Exists)

simenkg — Fri, 05 Dec 2014 16:44:07 GMT

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

Re: Concatenate Load Where New ID (Not Exists)

jeckstein — Wed, 14 Dec 2016 18:06:16 GMT

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"

Re: Concatenate Load Where New ID (Not Exists)

simenkg — Fri, 16 Dec 2016 11:20:22 GMT

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

Re: Concatenate Load Where New ID (Not Exists)

PrashantSangle — Fri, 16 Dec 2016 11:26:39 GMT

I don't think so

where not exist(uniqueField,uniqueField) must work

can you share any example which demonstrate example.

Regards,

Re: Concatenate Load Where New ID (Not Exists)

simenkg — Fri, 16 Dec 2016 13:24:31 GMT

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