topic Re: Check if field was ever a certain value in QlikView

Check if field was ever a certain value

Tue, 27 May 2014 19:44:09 GMT

Hi, I'm working with a set of data that contains information pertaining to accounts. Each account has a row of data that represents the the account's standing at the end of the month. Since my database has been collecting data on these accounts for several months, each account has several rows of data. I am interested in writing a script that would allow me to go check if a certain field was ever a specific value for each account. For example, say I have 10 rows for account 1 - I would like to look at a specific field in all of those 10 rows and determine if that field ever contained 'x' as a value. Is this possible? Thank you.

Re: Check if field was ever a certain value

Tue, 27 May 2014 19:49:40 GMT

Do you only want to know if it ever contained that value, or would you like to know when it contained the value?

Re: Check if field was ever a certain value

Tue, 27 May 2014 19:50:40 GMT

Both if that's possible

Re: Check if field was ever a certain value

Tue, 27 May 2014 19:52:51 GMT

This could be helpful:

FieldIndex(fieldname , value )

Returns the position of the field value value found in the field fieldname (by load order). If value cannot be found among the field values, 0 is returned. fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes.

Example:

FieldIndex( 'Name', 'John Doe' )

Re: Check if field was ever a certain value

swuehl — Tue, 27 May 2014 19:54:56 GMT

In your script, what about just using a where clause to filter your data?

Or in the front end UI, just create a list box for that field and select the value? Then, in a table box / chart that shows your customer data, you should see the data of interest.

I am probably not fully understanding what you want to achieve, some sample lines of your data and your expected outcome might help here.

Re: Check if field was ever a certain value

maxgro — Tue, 27 May 2014 19:56:27 GMT

maybe

// define target by account

map:

mapping load * inline [

acc, trg

1,x

2,y

....

];

// add a flag field to identify account-target

load

account,

if(fieldtocheck=applymap('map', account, 'nomatch'),1,0) as flag,

fieldtocheck,

.....

from

....

;

Re: Check if field was ever a certain value

Anonymous — Tue, 27 May 2014 19:57:52 GMT

I'm sure there are many ways. For example concatenate all values of the field, and use index() to see if there is a match.

=index(concat({1} distinct Field, '|'),'|X|')>0

If result is 0, there is no match.

Re: Check if field was ever a certain value

Tue, 27 May 2014 20:54:28 GMT

This seems like a simple solution, but how would I be able to separate this by each account so that I get a true or false for each of them?

Re: Check if field was ever a certain value

Anonymous — Wed, 28 May 2014 12:17:12 GMT

Same thing. Create a chart (straight table) with Account as dimension, and expression:
if(index(concat({1} distinct Field, '|'),'|X|')=0,0,1)

I use {1} here to ignore selections, but if you want the result to depend on selections, remove it:
if(index(concat(distinct Field, '|'),'|X|')=0,0,1)