topic Re: EXISTS() - BUG or WAD in QlikView

EXISTS() - BUG or WAD

luciancotea — Thu, 16 May 2013 07:09:26 GMT

Hello all,

If you run the following script:

TABLE1:
LOAD * INLINE [
    F1, F2
    1, a
    2, b
    3, c
];
  
LOAD * INLINE [
    F1, F2
    4, d
    4, e
]
WHERE NOT Exists(F1)
;

the last line from second table (4, e) will not be loaded despite the fact that 4 does not exists in TABLE1.

What do you think, is it a BUG or WAD (Work as Designed)?

I say it's a bug.

Re: EXISTS() - BUG or WAD

swuehl — Thu, 16 May 2013 07:13:32 GMT

WAD, because at the moment you load the last line from second table, the first line is already part of your data model, so F1 value 4 exists.

Rename F1 in your first load and use exists with two parameter:

TABLE1: LOAD F1 as F11, F2 INLINE [ F1, F2 1, a 2, b 3, c ]; LOAD F1 as F11, F2 INLINE [ F1, F2 4, d 4, e ] WHERE NOT Exists(F11, F1) ;

Re: EXISTS() - BUG or WAD

luciancotea — Thu, 16 May 2013 07:23:51 GMT

Thank you for your input. I would like to discuss the behaviour, not the workaround.

I don't think it should behave like this because it's not natural/intuitive.

When I say:

"Load all the rows from TABLE2 into TABLE1 where values from F1 are missing"

...I don't expect to load only the first row for each missing value.

Re: EXISTS() - BUG or WAD

swuehl — Thu, 16 May 2013 07:44:11 GMT

The where clause is executed per input record. As the last record comes in, the condition is false.

WAD, even / though it may contradict your personal user expectation.

Re: EXISTS() - BUG or WAD

luciancotea — Thu, 16 May 2013 07:48:55 GMT

You have a point.

Anybody else?

Re: EXISTS() - BUG or WAD

devans_1 — Thu, 16 May 2013 08:15:44 GMT

I think bloe might do the trick:

TABLE1:

LOAD F1, F2

INLINE [

F1, F2

1, a

2, b

3, c ];

LOAD F1, F2

INLINE [

F1, F2

4, d

4, e

5, f

1, g ]

where ISNULL (Lookup ('F2', 'F1', F1, 'TABLE1'));

Re: EXISTS() - BUG or WAD

Thu, 16 May 2013 08:53:08 GMT

Lucian,

1) swuehl/Stefan always has a point..whatever he says is authority for me....and I'm sure is authority for every community member

As you've said:"the last line from second table (4, e) will not be loaded despite the fact that 4 does not exists in TABLE1"

from QV help:

"exists Determines whether a specific field value exists in a specified field of the data loaded so far. Field is a name or a string expression evaluating to a field name. The field must exist in the data loaded so far by the script"

field value exists in a specified field: so it is not about a previous table but the field.

data loaded so far: 4 has been already been loaded in F1 when the turn for (4,e) comes to be loaded, so it is not loaded

So,it might fit in the category AI(Against Intution) but as per the categories suggested by you, it is WAD hence you can't compare it with intution as then you'll have 3 categories:

BUG,WAD, AI(Against Intuition)

Hope it helps.

Regards