Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- What is the best way to calculate with table variables and columns?

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

What is the best way to calculate with table variables and columns?

Aug 6, 2018 3:32 PM
(1430 views)

A JSL (TestJSL) script creates two table variables. Within the script they are being used to calculate a new column :D1 from the existing column :D0 by applying IF states etc..

Is there a better way to do this, than with the rather complex formula for :D1?

4 REPLIES 4

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: What is the best way to calculate with table variables and columns?

There are multiple ways to do this without having a table formula and table variables using set values.

Since your script used a formula, this alternative JSL uses a formula. If you can get by without a column formula, other methods can be used, including a script to prompt for quantile, threshold then recalculate qq and recalculate D1.

```
//Create Table Variable
dt = currentdatatable();
dt << Set Table Variable( "Quantile", 0.9 );
dt << Set Table Variable( "Threshold", 1 );
_xx = dt << get rows where(:P1 ==0 & :P2 <:Threshold);
dt << Set Table Variable("QQ", Quantile(dt:Quantile, dt:D0[_xx] ));
dt << New Column("D1", Numeric, Continuous,
Formula( If(:P1 ==0 & :P2 <:Threshold & :D0< :QQ, :D0) )
);
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: What is the best way to calculate with table variables and columns?

Thanks for your solution. It works fine. However, after the JSL was executed and I do readjust the table variable "Quantile" and "Threshold" the new column :D1 is only responding on changes on "Threshold" and not on changes on "Quantile". Could this be fixed?

@gzmorgan0 wrote:There are multiple ways to do this without having a table formula and table variables using set values.

Since your script used a formula, this alternative JSL uses a formula. If you can get by without a column formula, other methods can be used, including a script to prompt for quantile, threshold then recalculate qq and recalculate D1.

`//Create Table Variable dt = currentdatatable(); dt << Set Table Variable( "Quantile", 0.9 ); dt << Set Table Variable( "Threshold", 1 ); _xx = dt << get rows where(:P1 ==0 & :P2 <:Threshold); dt << Set Table Variable("QQ", Quantile(dt:Quantile, dt:D0[_xx] )); dt << New Column("D1", Numeric, Continuous, Formula( If(:P1 ==0 & :P2 <:Threshold & :D0< :QQ, :D0) ) );`

Thanks for your solution. It works fine. However, after the JSL was executed and I do readjust the table variable "Quantile" and "Threshold" the new column :D1 is only responding on changes on "Threshold" and not on changes on "Quantile".

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: What is the best way to calculate with table variables and columns?

Well your formula worked, so you could use that. I agree that formula is a difficult one to maintain.

Here is an alternative, add a script to the table. I added the JSL (after the wait(5) ) that shows how to change the quantile and threshold and run the script. Or interactively, change the 2 variables and click on Update D1.

Use whichever method you find easiest for you to use.

```
//Create Table Variable
dt = currentdatatable();
dt << Set Table Variable( "Quantile", 0.9 );
dt << Set Table Variable( "Threshold", 1 );
dt << New Column("D1", Numeric, Continuous);
dt << New Script ("Update D1",
dt = current data table();
_xx = dt << get rows where(:P1 ==0 & :P2 <:Threshold);
dt << Set Table Variable("QQ", Quantile(dt:Quantile, dt:D0[_xx] ));
qq = Quantile(dt:Quantile, dt:D0[_xx] );
//caption(char(qq)); wait(2); caption(remove); //uncomment to test
dt:D1 << Set Each Value(
If(:P1 ==0 & :P2 <:Threshold & :D0< qq, :D0, empty())
);
);
wait(5);
dt << Set Table Variable( "Quantile", 0.6 );
dt << Set Table Variable( "Threshold", 1.8 );
dt << Run Script("Update D1") ;
```

Highlighted
##

Thanks again. Now it works, by running the update script. The charm of the formula is that the data update is automatically done. Is it risky to do this with the formula for about 100k rows (avoiding errors ….)? If I stick with the formula would it be better to generate an column :DT in which to first part is being calculated If( :P1 == 0 & :P2 < :Threshold, :D0 ) What would a script look like in case there are several: D0a :D0b columns in order to generate the corresponding : D1a, .D1b columns?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: What is the best way to calculate with table variables and columns?

Article Labels

There are no labels assigned to this post.