259492_en-US

liliconcom

Preserve Order of Precedence in JSL

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

 

Re: Preserve Order of Precedence in JSL

The functions in the Statistical section will often ignore missing values, but not all. Check them out!

Re: Preserve Order of Precedence in JSL

Take it easy Vince.  We need your head firmly in place.

 

Thanks for the information about SUM Dan.  Hadn't thought of that one!

Re: Preserve Order of Precedence in JSL

Holy crap, this just made my head explode.  

Are there other operators that don't worry about missing. 

 

TO Wishlist!!

Re: Preserve Order of Precedence in JSL

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

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.

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=""></NEW>

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

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

Re: Preserve Order of Precedence in JSL

Certainly, attached. Thank you for the help!

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.

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

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