Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
mov
Esteemed Contributor III

Re: Check if field was ever a certain value

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)

8 Replies
Not applicable

Re: Check if field was ever a certain value

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

Re: Check if field was ever a certain value

Both if that's possible

Not applicable

Re: Check if field was ever a certain value

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

MVP
MVP

Re: Check if field was ever a certain value

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.

MVP
MVP

Re: Check if field was ever a certain value

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

     ....

     ;

mov
Esteemed Contributor III

Re: Check if field was ever a certain value

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

Re: Check if field was ever a certain value

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?

mov
Esteemed Contributor III

Re: Check if field was ever a certain value

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)

Community Browser