topic Re: Where not exists - How to SQL? in QlikView

Where not exists - How to SQL?

Tue, 15 Sep 2015 11:12:40 GMT

Hello,

I have a bizzare scenario.

I have a table (and many other rows i dont wanna mention)

T1:

D_Key

D_Key as Fod_Key_1

from

Transaktioner

where type = 'f'

concatenate (t1)

Load

D_Key

D_Key as Fod_Key_1

from Transaktioner

where type = 'd'

and not exists(Fod_Key_1,D_Key)

drop field FOD_KEY_1;

I simply cannot figure out how to repliacte this logic into SQL.

T1 consists of 100.000 rows

And the concatenated table consists of 6.000.000 rows.

As logic i would think when these two are concatenated there would be no less than 5.900.000 rows. However there is like only 5.600.000 rows when my scripts end?

Re: Where not exists - How to SQL?

Anonymous — Tue, 15 Sep 2015 11:53:06 GMT

hi thomas,

try:

T1:

D_Key

D_Key as Fod_Key_1

from

Transaktioner

where type = 'f'

concatenate (t1)

Load

D_Key

D_Key as Fod_Key_1

from Transaktioner

where type = 'd'

and not exists(D_Key)

Re: Where not exists - How to SQL?

Tue, 15 Sep 2015 11:56:07 GMT

I think u misunderstood my questions.

I wanna know how i can do the same logic in SQL.

Re: Where not exists - How to SQL?

Tue, 15 Sep 2015 12:38:33 GMT

Hi Thomas,

I am not 100% sure if I understood you correctly, but try this to statements.

T1:

Load

SQL Select DISTINCT

D_Key

D_Key as Fod_Key_1

from

Transaktioner

where

type = 'f'

Or type = 'd'

The first one may work if your requested fields are always the same (no matter if type is f or d).

T1:

Load

SQL Select DISTINCT

D_Key

D_Key as Fod_Key_1

from

Transaktioner

where

type = 'f'

Or (type = 'd' and not exists (Select 1 from Transaktioner t2 where type = f and Transaktioner.D_Key = t2.D_Key)

This may not be the fastest solution, but at least it should work.

Cheers,

Claus

Re: Where not exists - How to SQL?

Tue, 15 Sep 2015 12:46:58 GMT

But how come when i load this table for it self

t2:

load

D_key

,d_Key as Fod_Key_1

from transkationer

where not exists(fod_key_1,D_key)

it returns less rows? When fod_key_1, and d_key is the same?

when only loading one table?

so without exists it has 6.000.000 rows.

but the exists it has 5.800.000 rows?

Re: Where not exists - How to SQL?

sasiparupudi1 — Wed, 16 Sep 2015 08:54:11 GMT

try

load

D_key

,d_Key as Fod_Key_1

from transkationer

where not exists(D_key,D_key)

and see if it returns the correct records?

actually you can not access aliases in the conditions.. My assumption is that it is probably eliminating null values. do you perhaps have nulls in that column?

hth

Sasi