BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
lwx228
Super Contributor

How do I implement an excel like VLOOKUP using JSL?

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

 

2018-08-28_17-30-54.png

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User ms
Super User

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

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)
);
7 REPLIES 7
ms
Super User ms
Super User

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

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)
);
lwx228
Super Contributor

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

This is very useful. Thank you very much!
0 Kudos
lwx228
Super Contributor

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

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

 

 

2018-08-28_19-45-31.png

0 Kudos
ms
Super User ms
Super User

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

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

 

 

 

lwx228
Super Contributor

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

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!

0 Kudos
ms
Super User ms
Super User

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

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

 

lwx228
Super Contributor

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

a good way,thank you!
0 Kudos