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: 
Not applicable

Check if field was ever a certain value

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.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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)

View solution in original post

8 Replies
Not applicable
Author

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

Not applicable
Author

Both if that's possible

Not applicable
Author

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

swuehl
MVP
MVP

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.

maxgro
MVP
MVP

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

     ....

     ;

Anonymous
Not applicable
Author

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.

Not applicable
Author

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?

Anonymous
Not applicable
Author

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)