topic Re: Check if value exists in field in QlikView

Check if value exists in field

gsbeaton — Tue, 06 Sep 2011 12:05:52 GMT

Hi Folks,

I want to check if a value exists in a fild so I can display a message to users.

So my idea is to have a conditional show on a text object, something like this:

=if(FieldIndex('Payroll Detail Amount','blocked'),1,0)

However, fieldindex returns the index of the unfiltered position of the vlaue. I am looking for a function that will return the index (or a TRUE or FALSE) on a filtered list. EG, if the user has made selections and there are no records with a value of 'blocked' then return FALSE.

Many thanks for your help

George

Re: Check if value exists in field

gandalfgray — Tue, 06 Sep 2011 12:21:34 GMT

Hi George

If I understand your case:

You want to return 1 if the user has a value "blocked" in [Payroll Detail Amount] when all selections he has made is taken into account.

You can try this:

=count({<[Payroll Detail Amount]*={"blocked"}>} distinct [Payroll Detail Amount])

it will return 1 if blocked are among possible [Payroll Detail Amount] and 0 otherwise.

hth/gg

Check if value exists in field

gsbeaton — Tue, 06 Sep 2011 12:30:22 GMT

I hadn't even crossed my mind to use set analysis. Thank you for your help - this works perfectly.

George

Re: Check if value exists in field

Tue, 06 Sep 2011 17:26:08 GMT

Hello,

The solution given by GandalfGray is excellent. Even in my documents saved for later use.

Just for the study of QlikView would like to post a solution that usedin an application:

SUBSTRINGCOUNT ((';'& = CONCAT (DISTINCT FieldName,';')&';'),';'&$( VARIABLE )&';')

But as I said the solution is given by GandalfGray much better!

Hugs

Stive

Brasil