Choose Language Hide Translation Bar
Highlighted
lilysecret
Level III

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
lilysecret
Level III

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

View solution in original post

12 REPLIES 12
Highlighted
pmroz
Super User

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
lilysecret
Level III

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
pmroz
Super User

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
lilysecret
Level III

Re: Preserve Order of Precedence in JSL

Certainly, attached. Thank you for the help!

Highlighted
pmroz
Super User

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
pmroz
Super User

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
vince_faller
Super User

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

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.Capture.JPG

Dan Obermiller
Highlighted
lilysecret
Level III

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

View solution in original post

Article Labels

    There are no labels assigned to this post.