nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

optimizing an IF statement?

jmpbeginner

I have a list of specific values I am interested in identifying in my data set (specific values of the :MeasurementGroup column). I created a new column called "Value of Interest?" with a formula to generate an indicator: either YES or NO

dt1 << New Column( "Value of Interest?",

Character,

Nominal,

Formula( If(

:MeasurementGroup == "A", "YES",

:MeasurementGroup == "B", "YES",

:MeasurementGroup == "C", "YES",

:MeasurementGroup == "D", "YES",

:MeasurementGroup == "E", "YES",

:MeasurementGroup == "F", "YES",

:MeasurementGroup == "G", "YES",

:MeasurementGroup == "AA", "YES",

:MeasurementGroup == "BB", "YES",

:MeasurementGroup == "CC", "YES",

:MeasurementGroup == "ZZ", "YES",

:MeasurementGroup == "ABC", "YES",

:MeasurementGroup == "DEC", "YES",

:MeasurementGroup == "WSA", "YES",

:MeasurementGroup == "RSS", "YES",

:MeasurementGroup == "DVC", "YES",

:MeasurementGroup == "SFS", "YES",

:MeasurementGroup == "AAZ", "YES",

:MeasurementGroup == "ZZA", "YES",

:MeasurementGroup == "TTT", "YES",

"NO") )

);

My datatable has close to 1 million rows... and it seems to churn for a bit before populating. Is there a more efficient way to acheive this same result?

Any help is greatly appreciated! thanks

Accepted answers

The Match function is effective for multiple choices. I did a quick test: with a million rows Match, Loc and If complete in 2.1 sec, 6.9 sec and 10.2 sec, respectively.

dt << New Column("Value of Interest",

Character,

Nominal,

Formula(

Match(:MeasurementGroup,

"A", "YES",

"B", "YES",

"C", "YES",

"D", "YES",

"E", "YES",

"F", "YES",

"G", "YES",

"AA", "YES",

"BB", "YES",

"CC", "YES",

"ZZ", "YES",

"ABC", "YES",

"DEC", "YES",

"WSA", "YES",

"RSS", "YES",

"DVC", "YES",

"SFS", "YES",

"AAZ", "YES",

"ZZA", "YES",

"TTT", "YES",

"NO"

)

);

All comments

txnelson

I would try using the LOC() function

if(N Rows(Loc({"A","B","C",...............},MeasurementGroup))>0,"Yes","NO")

The Match function is effective for multiple choices. I did a quick test: with a million rows Match, Loc and If complete in 2.1 sec, 6.9 sec and 10.2 sec, respectively.

dt << New Column("Value of Interest",

Character,

Nominal,

Formula(

Match(:MeasurementGroup,

"A", "YES",

"B", "YES",

"C", "YES",

"D", "YES",

"E", "YES",

"F", "YES",

"G", "YES",

"AA", "YES",

"BB", "YES",

"CC", "YES",

"ZZ", "YES",

"ABC", "YES",

"DEC", "YES",

"WSA", "YES",

"RSS", "YES",

"DVC", "YES",

"SFS", "YES",

"AAZ", "YES",

"ZZA", "YES",

"TTT", "YES",

"NO"

)

);

msharp

Give this man an award! This is great.

jmpbeginner

Jim, thank you for the quick response! I was able to implement the LOC function and see an immediate improvement in speed!

MS, thank you for improving upon this further and teaching me the MATCH function (and providing the speed comparison)!

brady_brady

Hi JMPBeginner,

**** Please refer to the attached .jsl file to see these scripts as intended. I have been having trouble getting my posts to display correctly, and if you copy/paste or type in the scripts that appear below, I am not sure if they will work. They look incorrect when I post them, but they look OK when I edit, so I am not sure what to do except attach a script file. My apologies!

*****

If you are willing to use value labels (for large tables, this can save a good deal of disk space, as well), you have another couple of speedy options:

1) A formula-based approach using the Contains() function:

dt << New Column( "y/n 2",

formula(

Contains(

{"A", "B", "C", "D", "E", "F", "G", "AA", "BB", "CC", "ZZ", "ABC", "DEC", "WSA", "RSS", "DVC", "SFS", "AAZ", "ZZA", "TTT"},

:MeasurementGroup

) != 0

<<Add Column Properties( value labels( {0, 1}, {"NO", "YES"} ) )

);

2) A non-formula based approach: JMP works with tables very quickly; the speed of this approach shocked me a little, given that a completely new table must be created and closed:

dt = Current Data Table();

dt2 = New Table( "mt", invisible );

Column( dt2, 1 ) << datatype( character ) << set name( "MeasurementGroup" ) << set values(

{"A", "B", "C", "D", "E", "F", "G", "AA", "BB", "CC", "ZZ", "ABC", "DEC", "WSA", "RSS", "DVC", "SFS", "AAZ", "ZZA", "TTT"}

);

dt2 << New Column( "y/n", <<set each value( 1 ) );

dt << New Column( "y/n", <<set each value( 0 ) );

dt << Update( With( dt2 ), Match Columns( :Measurement Group = :MeasurementGroup ), Add Columns from Update table( None ) );

Column( dt, "y/n" ) << Add Column Properties( value labels( {0, 1}, {"NO", "YES"} ) );

Close( dt2, nosave );

On a million row table, I was getting runtimes of ~ 2.4 seconds for the Match function. (MS's computer seems to be faster than mine

The runtimes of approach 1 above were ~ 0.8 seconds, while the runtimes of approach 2 were ~ 0.4 seconds.

Cheers,

Brady

IfOptimization.jsl

pmroz

Looks good Brady!

BTW when I post code I copy/paste the JSL into a word document, and then copy/paste from there to the forum. That preserves the look of the JSL editor.

For example:

dt << New Column( "y/n 2",

formula(

Contains({"A", "B", "C", "D", "E", "F", "G", "AA", "BB", "CC", "ZZ", "ABC", "DEC", "WSA",

"RSS", "DVC", "SFS", "AAZ", "ZZA", "TTT"}, :MeasurementGroup

) != 0

);

brady_brady

Thanks Peter, I will use this trick from now on when I post.

Cheers,

Brady