topic Re: Identify type of joining in QlikView

Identify type of joining

Anonymous — Mon, 19 Dec 2016 17:11:44 GMT

Colleagues, good day!

Simple question:

Script loads first table:

Id	Field1
1	A
2	A
3	A
4	A
5	A
6	A

Then i must load second table but only data for those Id which are not contain in first table:

Second:

Id	Field2
1	B
2	B
3	B
4	B
8	B
9	B

In result i must get only:

Id	Field2
8	B
9	B

Thanks.

Re: Identify type of joining

Anonymous — Mon, 19 Dec 2016 17:14:51 GMT

Try on your second load :

where not exists (Id)

Re: Identify type of joining

sunny_talwar — Mon, 19 Dec 2016 17:16:01 GMT

May be like this:

LOAD Id,

Field1

FROM....

LOAD Id,

Field2

FROM ...

Where not Exists(Id);

DROP Table A;

Re: Identify type of joining

Anonymous — Mon, 19 Dec 2016 17:18:10 GMT

Thanks!

It works.

But how QV Know, what previous table must be checked for already exist values? (Id in this case)

Re: Identify type of joining

johnca — Mon, 19 Dec 2016 17:19:08 GMT

Maybe like this?

temp1:

Load * Inline [

Id, Field1

1,A

2,A

3,A

4,A

5,A

6,A

];

TEMP2:

NoConcatenate

Load *

Where Not Exists (Id);

Load * Inline [

Id, Field1

1,B

2,B

3,B

4,B

8,B

9,B

];

Drop Table temp1;

Re: Identify type of joining

Anonymous — Mon, 19 Dec 2016 17:20:51 GMT

QlikView checks the data model for the field Id and looks up values already loaded into the field. This is done at the field level, not the table level.

Re: Identify type of joining

sunny_talwar — Mon, 19 Dec 2016 17:21:10 GMT

This is how Exist function works where it checks against all possible value within a field previously loaded in the script.

Re: Identify type of joining

marcus_sommer — Mon, 19 Dec 2016 17:23:34 GMT

Qlik checked for this the values within the symbol-tables, see here what is meant: Symbol Tables and Bit-Stuffed Pointers.

- Marcus

Re: Identify type of joining

Anonymous — Mon, 19 Dec 2016 17:26:23 GMT

Thanks))

And is is possible to check for some fields combinations?

For ex:

First table:

Id	F1
1	A
2	A
3	A
4	A
5	B
6	B

Second:

Id	F1
1	3
2	ed
3	fd
4	dfd
5	fdf
6	B
7	u

for where not Exists(Id&F1) i must get only:

Id	F1
5	fdf
7	u

but it returns empty result

Re: Identify type of joining

Anonymous — Mon, 19 Dec 2016 17:27:14 GMT

Thanks!

Re: Identify type of joining

Anonymous — Mon, 19 Dec 2016 17:27:40 GMT

Thanks, dear.

Re: Identify type of joining

sunny_talwar — Mon, 19 Dec 2016 17:29:55 GMT

Why is 5 included, but 1 or 2 not included in your final output?

Re: Identify type of joining

Anonymous — Mon, 19 Dec 2016 17:36:19 GMT

Sorry, it's my mistake:

Necessary result:

Id	F1
1	3
2	ed
3	fd
4	dfd
5	fdf
7	u

Re: Identify type of joining

sunny_talwar — Mon, 19 Dec 2016 17:43:17 GMT

May be like this:

LOAD Id,

Field1,

Id&Field1 as Check

FROM....

NoConcatenate

LOAD Id,

Field1

FROM ...

Where not Exists(Check, Id&Field1);

DROP Table A;

Re: Identify type of joining

Anonymous — Mon, 19 Dec 2016 18:01:18 GMT

Perfect!

Thanks!