topic Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP in Discussions

How Do I Transfer Excel COUNTIF and MATCH functions into JMP

sellerm — Wed, 14 Dec 2016 18:49:44 GMT

I am having an issue in scripting the JSL code for two functions (see screen shot) which I currently have in Excel but want to transfer over into JMP. The data is found in the attached file JMP_Test_Average2.xlsx and the functions I wish to transfer to JMP are as follows:

column VS has a COUNTIF function; =COUNTIF(C3:N3,"-1")
- I want to know the number of instances -1 appears in each row (e.g. in row 3 the number of instances of -1 are 2). Column m(AB) is ignored in the function but it is impoirtant as it is part of the data sequence.
column PD has a MATCH function; =(MATCH("-1",C3:N3,0))+1

I want to know where the first instance of -1 appears in each row (e.g. in row 3 the first -1 appears in column m(LM) or column 12 when considering columns m(AB) to m(MN)). Column m(AB) is ignored in the functions but it is impoirtant as it is part of the data sequence.

I would like to know how to script these functions in JSL so I can update my JMP table and not have to complete the calculations in Excel and then bring the results over into my JMP table.

Any help would be much appreciated.

Thanks.

Mike

Data as found in file JMP_Test_Average2.xlsx

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

vince_faller — Wed, 14 Dec 2016 19:56:37 GMT

This should give you a basic Idea.

Names Default to here(1);
dt = new table("Test", add rows(10),
New Column("AB", formula(if(random uniform()<=.8, 1, -1))),
New Column("BC", formula(if(random uniform()<=.8, 1, -1))),
New Column("CD", formula(if(random uniform()<=.8, 1, -1))),
New Column("DE", formula(if(random uniform()<=.8, 1, -1))),
New Column("EF", formula(if(random uniform()<=.8, 1, -1))),
);

cols = dt << Get Column Reference(dt << Get Column Names);

dt << new Column("VS", formula(nrows(Loc(Evallist({:AB, :BC, :CD, :DE, :EF}), -1))));
dt << New Column("PD", formula(Contains(Evallist({:AB, :BC, :CD, :DE, :EF}), -1)));

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

txnelson — Thu, 15 Dec 2016 16:31:49 GMT

Here are the 2 formulae that I came up with to replicate your spreadsheet

For YS

Sum(
	:Name( "m(AB)" ) == -1,
	:Name( "m(BC)" ) == -1,
	:Name( "m(CD)" ) == -1,
	:Name( "m(EF)" ) == -1,
	:Name( "m(FG)" ) == -1,
	:Name( "m(GH)" ) == -1,
	:Name( "m(HI)" ) == -1,
	:Name( "m(IJ)" ) == -1,
	:Name( "m(JK)" ) == -1,
	:Name( "m(KL)" ) == -1,
	:Name( "m(LM)" ) == -1,
	:Name( "m(MN)" ) == -1
)

For PD

Contains(
	Char( :Name( "m(AB)" ) ) || Char( :Name( "m(BC)" ) ) ||
	Char( :Name( "m(CD)" ) ) || Char( :Name( "m(EF)" ) ) ||
	Char( :Name( "m(FG)" ) ) || Char( :Name( "m(GH)" ) ) ||
	Char( :Name( "m(HI)" ) ) || Char( :Name( "m(IJ)" ) ) ||
	Char( :Name( "m(JK)" ) ) || Char( :Name( "m(KL)" ) ) ||
	Char( :Name( "m(LM)" ) ) || Char( :Name( "m(MN)" ) ),
	"-1"
)

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

sellerm — Thu, 15 Dec 2016 16:34:41 GMT

Thanks for your help, much appreciated.

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

sellerm — Thu, 15 Dec 2016 16:36:40 GMT

Thanks for your help as well Jim, you and Vince solved my issue.

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

lwx228 — Sun, 16 Dec 2018 15:11:19 GMT

Here I find the answer and solve my own problems.Thanks!

Contains( Eval List( :BC[Index(Row()-3,Row())] ), -1 )

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

lwx228 — Mon, 17 Dec 2018 07:42:24 GMT

howdy ! I'm dying to know:
If column "AB" has more than one "-1", how do figure out the number of rows that are within the range of the last "-1" in this range?
Thanks!

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

lwx228 — Mon, 17 Dec 2018 07:45:09 GMT

I tried to write code like this, but it didn't work.

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

txnelson — Mon, 17 Dec 2018 13:59:42 GMT

This might be the formula you are looking for

x = 5 - Contains( Eval List( :ab[Index( Row(), Row() - 3 )] ), -1 );
If( x == 5, x = 0 );

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

lwx228 — Mon, 17 Dec 2018 14:54:50 GMT

Thank Jim!

x=5-Contains(Eval List(:ab[Index(Row(),Row()-3)]),-1);
If(x==5,0,x);

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

vince_faller — Mon, 17 Dec 2018 17:31:32 GMT

//this gives you a vector of items that are -1
vector_of_neg1 = loc(Aslist(:AB[row()-3::row()]`), -1);
//then you just have to select the last one
n = nitems(vector_of_neg1);
if(n, 
	vector_of_neg1[n]
, //else 0
	0
);

Or you can use the start argument to look from the back.

Contains(  :AB[Index( Row() - 3, Row() )] , -1, -1 )

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

lwx228 — Fri, 30 Aug 2019 10:46:59 GMT

How can the column name table {:AB, :BC, :CD, :DE, :EF} of each column in the code be automatically generated by the code?

{:AB, :BC, :CD, :DE, :EF}

Thanks!

I've tried the following code to have the same effect, but it still doesn't generate automatically.

Evallist({As Column(1),As Column(2),As Column(3),As Column(4),As Column(5)})

Re: How Do I Transfer Excel COUNTIF and MATCH functions into JMP

lwx228 — Fri, 30 Aug 2019 10:49:33 GMT

dt << new Column("VS", formula(nrows(Loc(Evallist({:AB, :BC, :CD, :DE, :EF}), -1))));

How does this code need to be modified to count the -1 of the line and the 2 lines above (3 lines in total)?

Thanks!