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

How to compute a match with JSL?

lwx228

Hello, everyone!

In excel, "=offset(,match(,,)-1,match(,,)-1,,)" functions can be used to calculate, but the speed is slow when the data is large.

How to do this with JSL?Thank you!

Accepted answers

Here are a few ways, to stack with a variable number of columns, if you are using match in excel today then one of the first two methds will likely work:

//If the column positions do not change
d3_1 = d1 << Stack(
	columns( Concat( [2 3], 4::10 )),
	Source Label Column( "year" ),
	Stacked Data Column( "data" ),
	Drop all other columns( 1 ),
	Keep( :state )
);


//If you want all columns
d1_clean = ( d1 << Subset( All rows, columns( 1::10 ) ) );
d3_2 = d1_clean << Stack(
	columns( 2:: ncol(d1_clean) ),
	Source Label Column( "year" ),
	Stacked Data Column( "data" ),
	Drop all other columns( 1 ),
	Keep( :state )
);


//If the column names are in a specific format

// - get column names
cn = d1 << Get Column Names( string );

// - Find which columns match a format
//   (jmp does have some patter matching tools but I like regex, check out regexr.com)
matches = repeat(0, N Items(cn));
for(c=1, c<= N Items(cn), c++, if(!is missing(Regex(cn[c], "^\d+$")), matches[c] = 1));

// - Stack
d3_3 = d1 << Stack(
	columns( loc(matches) ),
	Source Label Column( "year" ),
	Stacked Data Column( "data" ),
	Drop all other columns( 1 ),
	Keep( :state )
);

All comments

lwx228

d1 = Open( "$SAMPLE_DATA/Presidential Elections.jmp" );
d2 = New Table( "offset",
Add Rows( 8 ),
New Column( "State",
Character,
"Nominal",
Set Values(
{"Wyoming", "Montana", "Montana", "Alaska", "Wisconsin", "Nebraska", "Nevada", "Montana"}
)
),
New Column( "year",
Character,
"Nominal",
Set Values( {"1988", "1988", "1988", "1992", "2000", "1992", "2004", "1980"} )
),
New Column( "data", Character, "Nominal", ),

);

lwx228

Calculate the value of "data" in table "offset" based on the data in table "Presidential Elections".

Thank you!

Since you are worried about speed with large tables, you might create a 'lookup table' in a long format so there is one row per value and then use with Join or Update to do the lookup. Here is how that might look:

Names default to here( 1 );

d1 = Open( "$SAMPLE_DATA/Presidential Elections.jmp" );
d2 = New Table( "offset",
	Add Rows( 8 ),
	New Column( "State",
		Character,
		"Nominal",
		Set Values(
			{"Wyoming", "Montana", "Montana", "Alaska", "Wisconsin", "Nebraska", "Nevada", "Montana"}
		)
	),
	New Column( "year",
		Character,
		"Ordinal",
		Set Values( {"1988", "1988", "1988", "1992", "2000", "1992", "2004", "1980"} )
	),
	New Column( "data",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 12, 1 ))

);

//Move data to a long format
d3 = d1 << Stack(
	columns(
		:Name( "1980" ),
		:Name( "1984" ),
		:Name( "1988" ),
		:Name( "1992" ),
		:Name( "1996" ),
		:Name( "2000" ),
		:Name( "2004" ),
		:Name( "2008" ),
		:Name( "2012" )
	),
	Source Label Column( "year" ),
	Stacked Data Column( "data" )
);
d3 << Set Name( "Long format" );
d3:year << Set Modeling Type("Ordinal");

//'Lookup' the values:
d2 << Update( with(d3), Match Columns( :state=:state, :year=:year ), Add Columns from Update table( None ));

d3 << Close window;

lwx228

good job !Thank you!

lwx228

If these columns are many, such as more than 1000……Let's say it's a date.

how do these columns implement the d3 table setup in the form of a variate loop.Thank you!