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

- JMP User Community
- :
- Discussions
- :
- Preserve Order of Precedence in JSL

- 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

Apr 21, 2020 10:25 AM
(1916 views)

Hello,

I am trying to write a script that creates a number of total columns for a series of products. Odd numbered columns represent total number of ounces and even numbered columns hold the number of containers. I need to multiply each pair and sum the results. I came up with a simple script below, but the resulting formula in the column doesn't contain the parenthesis needed to preserve order of precedence and the column is empty of data. Obviously I'm missing something but I can't figure out what. Attached is sample data and the script.

Thank you!

```
dt = currentdatatable();
New Column( "DET Current Total TD", Numeric, "Continuous", Format( "Best", 12 ), Formula(
(:Name( "DET_P51_AMT_NEW_1. TD" ) * :Name( "DET_P51_AMT_NEW_2. TD" )) +
(:Name( "DET_P51_AMT_NEW_3. TD" ) * :Name( "DET_P51_AMT_NEW_4. TD" )) +
(:Name( "DET_P51_AMT_NEW_5. TD" ) * :Name( "DET_P51_AMT_NEW_6. TD" )) +
(:Name( "DET_P51_AMT_NEW_7. TD" ) * :Name( "DET_P51_AMT_NEW_8. TD" )) +
(:Name( "DET_P51_AMT_NEW_9. TD" ) * :Name( "DET_P51_AMT_NEW_10. TD" )) ));
```

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

Yes, I ended up using "Sum" to create the formula and it worked like a charm. Thanks everyone!

```
dt << New Column( "DET TOTAL CURRENT Ajax Powder",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
Sum(
:"DET_P1_AMT_NEW_1. Ajax Powder" * :"DET_P1_AMT_NEW_2. Ajax Powder",
:"DET_P1_AMT_NEW_3. Ajax Powder" * :"DET_P1_AMT_NEW_4. Ajax Powder",
:"DET_P1_AMT_NEW_5. Ajax Powder" * :"DET_P1_AMT_NEW_6. Ajax Powder",
:"DET_P1_AMT_NEW_7. Ajax Powder" * :"DET_P1_AMT_NEW_8. Ajax Powder",
:"DET_P1_AMT_NEW_9. Ajax Powder" * :"DET_P1_AMT_NEW_10. Ajax Powder"
)
)
);
```

12 REPLIES 12

Highlighted
##

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

Re: Preserve Order of Precedence in JSL

The parentheses look OK. You need to send the message to the table:

```
dt = currentdatatable();
dt << New Column( "DET Current Total TD", Numeric, "Continuous", Format( "Best", 12 ), Formula(
(:Name( "DET_P51_AMT_NEW_1. TD" ) * :Name( "DET_P51_AMT_NEW_2. TD" )) +
(:Name( "DET_P51_AMT_NEW_3. TD" ) * :Name( "DET_P51_AMT_NEW_4. TD" )) +
(:Name( "DET_P51_AMT_NEW_5. TD" ) * :Name( "DET_P51_AMT_NEW_6. TD" )) +
(:Name( "DET_P51_AMT_NEW_7. TD" ) * :Name( "DET_P51_AMT_NEW_8. TD" )) +
(:Name( "DET_P51_AMT_NEW_9. TD" ) * :Name( "DET_P51_AMT_NEW_10. TD" )) ));
```

Highlighted
##

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

Re: Preserve Order of Precedence in JSL

Thanks, I tried that and the parentheses still disappear.

```
:DET_P51_AMT_NEW_1. TD * :DET_P51_AMT_NEW_2. TD + :DET_P51_AMT_NEW_3. TD * :DET_P51_AMT_NEW_4. TD +
:DET_P51_AMT_NEW_5. TD * :DET_P51_AMT_NEW_6. TD + :DET_P51_AMT_NEW_7. TD * :DET_P51_AMT_NEW_8. TD +
:DET_P51_AMT_NEW_9. TD * :DET_P51_AMT_NEW_10. TD
```

Highlighted
##

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

Re: Preserve Order of Precedence in JSL

JMP removed the parentheses because they were unnecessary - the order of precedence is multiplication first, followed by addition. That follows how your parentheses go. I tried a small table and the numbers check out.

Can you attach your table? Your project file looked for a nonexistent file.

Highlighted
##

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

Re: Preserve Order of Precedence in JSL

Certainly, attached. Thank you for the help!

Highlighted
##

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

Re: Preserve Order of Precedence in JSL

It's failing because of all the missing values. You can either write a more complicated formula or convert all missing values to 0.

```
dt = currentdatatable();
col_list = {"DET_P51_AMT_NEW_1. TD", "DET_P51_AMT_NEW_2. TD",
"DET_P51_AMT_NEW_3. TD", "DET_P51_AMT_NEW_4. TD",
"DET_P51_AMT_NEW_5. TD", "DET_P51_AMT_NEW_6. TD",
"DET_P51_AMT_NEW_7. TD", "DET_P51_AMT_NEW_8. TD",
"DET_P51_AMT_NEW_9. TD", "DET_P51_AMT_NEW_10. TD"};
for (i = 1, i <= nitems(col_list), i++,
one_col = col_list[i];
for (k = 1, k <= nrows(dt), k++,
if (is missing(column(dt, one_col)[k]),
column(dt, one_col)[k] = 0;
);
);
);
```

Highlighted
##

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

Re: Preserve Order of Precedence in JSL

This code is probably more efficient.

```
dt = currentdatatable();
col_list = {"DET_P51_AMT_NEW_1. TD", "DET_P51_AMT_NEW_2. TD",
"DET_P51_AMT_NEW_3. TD", "DET_P51_AMT_NEW_4. TD",
"DET_P51_AMT_NEW_5. TD", "DET_P51_AMT_NEW_6. TD",
"DET_P51_AMT_NEW_7. TD", "DET_P51_AMT_NEW_8. TD",
"DET_P51_AMT_NEW_9. TD", "DET_P51_AMT_NEW_10. TD"};
for (i = 1, i <= nitems(col_list), i++,
one_col = col_list[i];
missing_rows = dt << get rows where(is missing(as column(dt, one_col)));
if (nrows(missing_rows) > 0,
column(dt, one_col)[missing_rows] = 0;
);
);
```

Highlighted
##

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

Re: Preserve Order of Precedence in JSL

Another method to deal with the missings.

And to elaborate, values operated on (addition, subtraction, comparison) by a missing value usually return a missing result.

```
Names default to here(1);
dt = currentdatatable();
// get all rows as matrix
m = dt[0, {"DET_P51_AMT_NEW_1. TD", "DET_P51_AMT_NEW_2. TD"
, "DET_P51_AMT_NEW_3. TD", "DET_P51_AMT_NEW_4. TD"
, "DET_P51_AMT_NEW_5. TD", "DET_P51_AMT_NEW_6. TD"
, "DET_P51_AMT_NEW_7. TD", "DET_P51_AMT_NEW_8. TD"
, "DET_P51_AMT_NEW_9. TD", "DET_P51_AMT_NEW_10. TD"
}];
// set all the missing to a value, 0 in this case
m[Loc(ismissing(m))] = 0;
// reset the table to the matrix
dt[0, {"DET_P51_AMT_NEW_1. TD", "DET_P51_AMT_NEW_2. TD"
, "DET_P51_AMT_NEW_3. TD", "DET_P51_AMT_NEW_4. TD"
, "DET_P51_AMT_NEW_5. TD", "DET_P51_AMT_NEW_6. TD"
, "DET_P51_AMT_NEW_7. TD", "DET_P51_AMT_NEW_8. TD"
, "DET_P51_AMT_NEW_9. TD", "DET_P51_AMT_NEW_10. TD"
}]; = m;
dt <<New Column( "DET Current Total TD", Numeric, "Continuous", Format( "Best", 12 ), Formula(
(:Name( "DET_P51_AMT_NEW_1. TD" ) * :Name( "DET_P51_AMT_NEW_2. TD" )) +
(:Name( "DET_P51_AMT_NEW_3. TD" ) * :Name( "DET_P51_AMT_NEW_4. TD" )) +
(:Name( "DET_P51_AMT_NEW_5. TD" ) * :Name( "DET_P51_AMT_NEW_6. TD" )) +
(:Name( "DET_P51_AMT_NEW_7. TD" ) * :Name( "DET_P51_AMT_NEW_8. TD" )) +
(:Name( "DET_P51_AMT_NEW_9. TD" ) * :Name( "DET_P51_AMT_NEW_10. TD" ))
));
```

Vince Faller - Predictum

Highlighted
##

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

Re: Preserve Order of Precedence in JSL

These are all wonderful scripting examples of how to deal with the missing values. But there is a function that will take care of this: Sum.

The Sum function can be found in the Statistical grouping of the Formula Editor.

Dan Obermiller

Highlighted

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

Yes, I ended up using "Sum" to create the formula and it worked like a charm. Thanks everyone!

```
dt << New Column( "DET TOTAL CURRENT Ajax Powder",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
Sum(
:"DET_P1_AMT_NEW_1. Ajax Powder" * :"DET_P1_AMT_NEW_2. Ajax Powder",
:"DET_P1_AMT_NEW_3. Ajax Powder" * :"DET_P1_AMT_NEW_4. Ajax Powder",
:"DET_P1_AMT_NEW_5. Ajax Powder" * :"DET_P1_AMT_NEW_6. Ajax Powder",
:"DET_P1_AMT_NEW_7. Ajax Powder" * :"DET_P1_AMT_NEW_8. Ajax Powder",
:"DET_P1_AMT_NEW_9. Ajax Powder" * :"DET_P1_AMT_NEW_10. Ajax Powder"
)
)
);
```

Article Labels

There are no labels assigned to this post.