topic Find value in a field in QlikView

Find value in a field

Thu, 14 Apr 2011 17:01:45 GMT

Hi everyone,

I´d like to know how can I do to get a '0' if a value doesn´t exist in a field and a '1' if the value exist.

For example, I have this table:

T1:
LOAD * INLINE [
F1
'a'
'a'
'a'
'b'
'c'
];

And I´d like to know if 'z' value is in the F1 field, getting a '1' or a '0' if the value exist or not.

I´ve tried this

tmp:
LOAD COUNT(DISTINCT F1) as Found RESIDENT T1 WHERE F1='z';

LET value= peek('Found',0,'tmp');

But the value is NULL and not '0' when 'z' is not in the table.

Thanks a lot.

rwunderlich — Thu, 14 Apr 2011 18:54:55 GMT

You can change your expression to:

LET value= NoOfRows('tmp');

An easier way might be to skip creating the tmp table and use fieldIndex().

LET value = if(fieldIndex('F1', 'z') > 0, 1, 0);

Fri, 15 Apr 2011 07:56:00 GMT

Thanks a lot.