topic Re: Loading keys that not exist in table in QlikView

Loading keys that not exist in table

Thu, 07 May 2015 13:03:05 GMT

Hello!

After loading two tables I want to append into second table rows with keys of first table that not exist in second table.

How can I do this using EXISTS Function?

Please see small example attached.

Re: Loading keys that not exist in table

sunny_talwar — Thu, 07 May 2015 13:09:15 GMT

Try this Script:

t1:

LOAD id as idCheck,

LOAD * INLINE [

id, b

1, b1

2, b2

3, b3

4, b4

5, b5

];

t2: // Contains less rows than t1

LOAD * INLINE [

id, c

1, c1

2, c2

3, c3

];

// Trying to upload into t2 the absent keys from t1

Concatenate(t2)

LOAD

idCheck as id,

b as c

Resident t1

Where not Exists (id, idCheck);

DROP Table t1;

Re: Loading keys that not exist in table

sunny_talwar — Thu, 07 May 2015 13:10:12 GMT

Output will look like this:

Re: Loading keys that not exist in table

Thu, 07 May 2015 13:15:20 GMT

I understood the idea. But t1 and t2 have to be linked by 'id'.

Re: Loading keys that not exist in table

sunny_talwar — Thu, 07 May 2015 13:18:36 GMT

Are you trying to merge them into one table or keep two tables?

1st Case:

T2 with T1 and T2 Data

2nd Case:

T1 with T1 Data and

T2 with T1 and T2 Data

which one are you trying to achieve?

Best,

Sunny

Re: Loading keys that not exist in table

maxgro — Thu, 07 May 2015 19:53:58 GMT

t1:

LOAD id as idCheck,

LOAD * INLINE [

id, b

1, b1

2, b2

3, b3

4, b4

5, b5

];

t2: // Contains less rows than t1

LOAD * INLINE [

id, c

1, c1

2, c2

3, c3

];

// Trying to upload into t2 the absent keys from t1

Concatenate(t2)

LOAD

idCheck as id,

b as c

Resident t1

Where not Exists (id, idCheck);

NoConcatenate

load idCheck as id, b

resident t1;

DROP Table t1;

RENAME Table t to t1;