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

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

BSwid — Thu, 25 Apr 2019 18:59:09 GMT

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 )

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

txnelson — Thu, 25 Apr 2019 19:24:08 GMT

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

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

BSwid — Fri, 26 Apr 2019 14:37:53 GMT

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"?

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

txnelson — Thu, 25 Apr 2019 21:02:39 GMT

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

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

vince_faller — Thu, 25 Apr 2019 23:01:11 GMT

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

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

BSwid — Fri, 26 Apr 2019 14:35:52 GMT

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"

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

txnelson — Fri, 26 Apr 2019 14:14:10 GMT

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