Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Where not exists - How to SQL?

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?

5 Replies
neetha_p
Honored Contributor

Re: Where not exists - How to SQL?

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)

Not applicable

Re: Where not exists - How to SQL?

I think u misunderstood my questions.

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

Not applicable

Re: Where not exists - How to SQL?

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

Not applicable

Re: Where not exists - How to SQL?

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?

sasiparupudi1
Honored Contributor III

Re: Where not exists - How to SQL?

try

2:

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

Community Browser