topic Re: where not exists in QlikView

where not exists

ttal7878 — Wed, 25 Nov 2020 16:16:04 GMT

HI,

In the example attached, I try to use the where not exists

Because I do not have a unique field in my table, when I load the model it causes me to lose data,

only one row with ID = D is added to the main table

How can I load Table A and add the data from Table B but only the ID's that do not exist in Table A?

Re: where not exists

sunny_talwar — Mon, 11 Dec 2017 15:26:52 GMT

May be try this

LOAD * INLINE [

ID,DEP,Name

A,red

B,yellow

C,black

];

NoConcatenate

LOAD ID as ID1,

DEP,

Name;

LOAD * INLINE [

ID,DEP,Name

A,green

D,grey

D,white

];

Right Join (B)

LOAD ID1

Resident B

Where not Exists(ID, ID1);

Concatenate(A)

LOAD ID1 as ID,

DEP,

Name

Resident B;

DROP Table B;

Re: where not exists

johnca — Mon, 11 Dec 2017 15:49:09 GMT

Sunny, I modified it a bit...(Your code seemed to keep all rows instead of just the 2 where ID = D)

LOAD * INLINE [

ID,DEP,Name

A,red

B,yellow

C,black

];

NoConcatenate

LOAD ID as ID1,

DEP,

Name;

LOAD * INLINE [

ID,DEP,Name

A,green

D,grey

D,white

];

NoConcatenate

Load ID1 as ID,DEP,Name;

LOAD ID1,DEP,Name

Resident B

Where not Exists(ID, ID1);

Drop Tables A,B;

Re: where not exists

sunny_talwar — Mon, 11 Dec 2017 15:55:30 GMT

I think you might have misunderstood the problem. OP mentioned

"How can I load Table A and add the data from Table B but only the ID's that do not exist in Table A?" You don't have table A data anymore. I don't think this is the requirement, unless I have not understood the issue .

My code gave this

All of Table A and then only D from Table B.

Re: where not exists

johnca — Mon, 11 Dec 2017 16:02:07 GMT

Perhaps. I was under the impression she wanted just the ID's that existed in B that were NOT in A. This means only ID = D. My modification of your code gave this:

Re: where not exists

johnca — Mon, 11 Dec 2017 16:06:54 GMT

Dang, I just reread the initial post. You're right Sunny. <sigh>

Re: where not exists

ttal7878 — Mon, 11 Dec 2017 16:09:19 GMT

My intention is just as Sunny has described.

Thanks guys for your help, it helped me a lot

Re: where not exists

sunny_talwar — Mon, 11 Dec 2017 16:09:29 GMT

Re: where not exists

ttal7878 — Mon, 11 Dec 2017 16:38:20 GMT

Hey Sunny,

In your reply, when I look at the Table Viewer in the preview dialog,

I see that ID = 'D' has doubled itself, attached a screenshot

Re: where not exists

sunny_talwar — Mon, 11 Dec 2017 16:41:30 GMT

My bad, try this

LOAD * INLINE [

ID,DEP,Name

A,red

B,yellow

C,black

];

NoConcatenate

LOAD ID as ID1,

DEP,

Name;

LOAD * INLINE [

ID,DEP,Name

A,green

D,grey

D,white

];

Right Join (B)

LOAD DISTINCT ID1

Resident B

Where not Exists(ID, ID1);

Concatenate(A)

LOAD ID1 as ID,

DEP,

Name

Resident B;

DROP Table B;

Re: where not exists

ttal7878 — Mon, 11 Dec 2017 16:50:39 GMT