Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 25, 2019 11:59 AM
(5702 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

```
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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##
Re: If row column value equals any values of any array then... What is the general form of this formal?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Apr 25, 2019 1:46 PM
| Last Modified: Apr 26, 2019 7:37 AM
(5690 views)
| Posted in reply to message from txnelson 04-25-2019

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
##
Re: If row column value equals any values of any array then... What is the general form of this formal?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

```
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
##
Re: If row column value equals any values of any array then... What is the general form of this formal?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Apr 26, 2019 6:30 AM
| Last Modified: Apr 26, 2019 7:35 AM
(5643 views)
| Posted in reply to message from vince_faller 04-25-2019

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
##
Re: If row column value equals any values of any array then... What is the general form of this formal?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Apr 26, 2019 7:02 AM
| Last Modified: Apr 26, 2019 7:14 AM
(5640 views)
| Posted in reply to message from BSwid 04-26-2019

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

Jim

Article Labels

There are no labels assigned to this post.