topic Re: How do I implement an excel like VLOOKUP using JSL? in Discussions

How do I implement an excel like VLOOKUP using JSL?

lwx228 — Fri, 07 Dec 2018 19:55:11 GMT

How to find the students' sports names in the "big class families.jmp" table according to the students' names in the "Big Class.jmp" table.
and add columns in the "Big Class.jmp" table to display.Thank you very much!

d2 = Open( "$SAMPLE_DATA/big class families.jmp" );
d1 = Open( "$SAMPLE_DATA/Big Class.jmp" );

Re: How do I implement an excel like VLOOKUP using JSL?

ms — Mon, 27 Aug 2018 10:15:58 GMT

Update() by matching columns works well for this task. Either manually (Tables Menu) or by script.

Example:

d2 = Open("$SAMPLE_DATA/big class families.jmp");
d1 = Open("$SAMPLE_DATA/Big Class.jmp");
d1 << Update(
    With(d2),
    Match Columns(:name = :name),
    Add Columns from Update table(:sports)
);

Re: How do I implement an excel like VLOOKUP using JSL?

lwx228 — Mon, 27 Aug 2018 10:25:38 GMT

This is very useful. Thank you very much!

Re: How do I implement an excel like VLOOKUP using JSL?

lwx228 — Fri, 07 Dec 2018 19:55:42 GMT

Please continue your guidance:how to extract the value by matching both names simultaneously, like the offset function in excel.Thank you very much!

d1 = Open("$SAMPLE_DATA/Big Class.jmp");
d2 = New Table( "offset",
Add Rows( 8 ),
New Column( "name",
Character,
"Nominal",
Set Values( {"MARION", "MARION","MARION","MARION", "JUDY", "JUDY", "JUDY", "JUDY"} )
),
New Column( "obj",
Character,
"Nominal",
Set Values( {"sex", "age", "weight", "height", "sex", "age", "weight", "height"} )
)
);

Re: How do I implement an excel like VLOOKUP using JSL?

ms — Mon, 27 Aug 2018 12:14:07 GMT

It looks like you want to stack columns. Explore the Stack() command. However, in your example you have stacked columns of different data types (Character and numeric), which will not work unless data types are harmonized first.

d1 = Open("$SAMPLE_DATA/Big Class.jmp");
For(i = 1, i <= N Col(d1), i++,
    Column(d1, i) << data type("Character")
);
d1 << Stack(Columns(:age, :sex, :height, :weight));

Re: How do I implement an excel like VLOOKUP using JSL?

lwx228 — Mon, 27 Aug 2018 13:13:33 GMT

Thank you. That's a very efficient way. I'll keep that in mind.
But what if I only needed code to extract the data for the eight line names I gave?Want to learn one more way.

It's bothering you，thank you!

Re: How do I implement an excel like VLOOKUP using JSL?

ms — Mon, 27 Aug 2018 14:03:55 GMT

There are several ways. One is to simply delete unwanted rows from the stacked table.

d1 = Open("$SAMPLE_DATA/Big Class.jmp");
d2 = d1 << Stack(Columns(:age, :sex, :height, :weight));
d2 << delete rows(d2 << get rows where(:name != "MARION" & :name != "JUDY"));

Or just make a subset. The example below is without stacking (in JMP, it's rarely a good idea to mix widely different variables in the same column).

d1 = Open("$SAMPLE_DATA/Big Class.jmp");
d1 << Subset(rows(d1 << get rows where(:name == "MARION" | :name == "JUDY")));

Re: How do I implement an excel like VLOOKUP using JSL?

lwx228 — Mon, 27 Aug 2018 14:43:48 GMT

a good way，thank you!