Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
luciancotea
Specialist
Specialist

EXISTS() - BUG or WAD

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.

6 Replies
swuehl
MVP
MVP

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)
;

luciancotea
Specialist
Specialist
Author

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.

swuehl
MVP
MVP

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.

luciancotea
Specialist
Specialist
Author

You have a point.

Anybody else?

devans_1
Creator
Creator

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'));

Not applicable

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

2)

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