Choose Language Hide Translation Bar
Highlighted
Level IV

## If row column value equals any values of any array then... What is the general form of this formal?

How do I write an If statement (or whatever the right function is) to evaluated when a row's column value = any value in an array?

Example:

If(    :ColToCheck == [Any of these values] , then 1, else 0 )

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: If row column value equals any values of any array then... What is the general form of this formal?

``````Names default to here(1);
dt = open("\$SAMPLE_DATA\Big Class.jmp");
dt << new Column("Some Check", FOrmula(
//only doing !! so it's negates the output of contains (which is WHERE it is in the list)
!!Contains({59, 63, 48, 70}, :height)
));``````
Vince Faller - Predictum
6 REPLIES 6
Super User

## Re: If row column value equals any values of any array then... What is the general form of this formal?

Use the Loc() function to check to see if any values are the same in a list when compared.  When doing this with a matrix, convert the matrix to a list.

``````z=[1,2,3,4];
loc(as list(z),2);``````
Jim
Highlighted
Level IV

## Re: If row column value equals any values of any array then... What is the general form of this formal?

Thanks.

I'm trying to do something like this.

#1 it expects a numeric.

#2 the return is coming back all missing values.  I tried data type expression and numeric, couldn't run distribution to see if there were values other than missing value.

``````cust = ["customer1","customer2","customer3"];

If( Loc( As List( cust ), :Name( "Ship-To Customer" ) ),
1,
0
);``````

//update 4/26/19
the orignal post did not have cust defined, instead it showed

`z = ["customer1","customer2","customer3"];`

That's why TXNelson is asking what is "cust"?

Highlighted
Super User

## Re: If row column value equals any values of any array then... What is the general form of this formal?

1. A matrix can only contain numeric values.  Therefore, the matrix z needs to be specified as a List, not as a Matrix

2. I do not know what "cust" is.

3. Is this a column formula, or open JSL code?

4. Loc returns a matrix of the positions where the testing value is found in the List.  Therefore, you need to determine if more than 0 rows were returned to determine if a match has been found.

Below are a couple of script examples that might help you out

``````Names Default To Here( 1 );
dt = Current Data Table();

// As a formula in a new column
dt << New Column( "is present",
formula(
z = {"customer1", "customer2", "customer3"};
If( N Rows( Loc( z, :Name( "Ship-To Customer" ) ) > 0 ),
1,
0
);
)
);

// In open JSL
z = {"customer1", "customer2", "customer3"};
For( i = 1, i <= N Rows( dt ), i++,
If( N Rows( Loc( z, :Name( "Ship-To Customer" )[i] ) ) > 0,
1,
0
);
);``````
Jim
Highlighted
Super User

## Re: If row column value equals any values of any array then... What is the general form of this formal?

``````Names default to here(1);
dt = open("\$SAMPLE_DATA\Big Class.jmp");
dt << new Column("Some Check", FOrmula(
//only doing !! so it's negates the output of contains (which is WHERE it is in the list)
!!Contains({59, 63, 48, 70}, :height)
));``````
Vince Faller - Predictum
Highlighted
Level IV

## Re: If row column value equals any values of any array then... What is the general form of this formal?

Thank you both.  @txnelson TX got me over the hump last night.  I just converted characters to numeric and it gave the desired result.

I think @vince_faller 's solution is what I'm looking for. I tried it with characters and it worked

``````	New Column( "Some Check 2",
Numeric,
"Continuous",
Format( "Best", 12 ),                Formula( !!Contains( {"TIM", "ALICE", "JANE", "UDY"}, :name ) )
),		//  "UDY" is in there to verify contains will not return true for "JUDY"``````
Highlighted
Super User

## Re: If row column value equals any values of any array then... What is the general form of this formal?

Not directly.......that is why I use Loc().

Jim
Article Labels

There are no labels assigned to this post.