780244_en-US

liliconcom

Finding the correct format of name jsl

HI!

I've made a jsl script which is the following one : 

For Each Row(
	dt_fichier3, 
// Lire la valeur de la colonne "Process" et les offsets
	process_value = :Process;
	first_offset = :first_OFFSET;
	second_offset = :second_OFFSET;
	third_offset = :third_OFFSET;

// Vérifier et assigner les valeurs des offsets si elles ne sont pas vides ou "."
	If(
		(Char(first_offset) != "." & Char(first_offset) != "") | (Char(second_offset) != "." &
		Char(second_offset) != "") | (Char(third_offset) != "." & Char(third_offset) != ""), 

// Créer une nouvelle colonne dans le fichier1 si elle n'existe pas encore
		new_column_name = process_value || "_offset";
		dt_fichier1 << New Column(new_column_name, Numeric, Continuous);



// Définir la formule pour la nouvelle colonne
		Column(dt_fichier1, new_column_name) << Set Formula(
			Eval(
				Eval Expr(
					If(:Supplier == "FIRST",
						:Expr(process_value) + Expr(:first_OFFSET),
						If(:Supplier == "SECOND",
							:Expr(:Process) + Expr(:second_OFFSET),
							If(
								:Supplier == "THIRD",
									:Expr(:Process) + Expr(:third_OFFSET),
								,
									:Expr(:Process) // Valeur par défaut si aucun fournisseur ne correspond
							)
						)
					)
				)
			)
		);
	);

);

Edit (2024-08-08 jthi): Added jsl formatting

 

the problem is that the formula doesn't work because the column name appeart in the column info->Formula as "Column_name" or it should be "Column-name"n, I trid to use Name() function but it did not work

Automation and ScriptingData AccessData Exploration and VisualizationRe: Finding the correct format of name jsl

Build the formulas using expression evaluation Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute . This is usually my preferred method (or Eval(Substitute()) and NameExpr(AsColumn())

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

col1 = "height";
col2 = "weight";

new_col = Eval(EvalExpr(dt << New Column("mycol", Numeric, Continuous, Formula(
	Expr(NameExpr(AsColumn(dt, col1))) * Expr(NameExpr(AsColumn(dt, col2)))
))));

new_col << get formula;

Re: Finding the correct format of name jsl

Right,

But for example after the script compiled, if i take one column to see what its formula looks like I've got this :

If( :Supplier == "FIRST",
"Column_Name" + 4,
If( :Supplier == "SECOND",
"Column_Name" + 5,
If( :Supplier == "THIRD",
"Column_Name" + (-10),
"Column_Name"
)
)
)

Which doesn't work because it should be like that to work : 

If( :Supplier == "FIRST",
"Column_Name"n + 4,
If( :Supplier == "SECOND",
"Column_Name"n + 5,
If( :Supplier == "THIRD",
"Column_Name"n + (-10),
"Column_Name"n
)
)
)

How do I modified my script then ? Thank you

Re: Finding the correct format of name jsl

You are missing : from you columns, in your original script you have to modify at least these :Expr(:Process). I would also suggest using column names instead of references such as process_value = :Process (process_value = "Process" is what I prefer to use).

Re: Finding the correct format of name jsl

I don't get you, 

If I use process_value = "Process" then it will become a string and it won't take the column name.

Moreover, what do I nedd to change in these  :Expr(:Process)?

Re: Finding the correct format of name jsl

I did show one technique to turn those (strings) back to columns inside formulas in my earlier reply.

Re: Finding the correct format of name jsl

Ah... Using :Process is correct in that location as you are using For Each Row loop. Still same method apply to convert those values you get from the columns back into columns

Re: Finding the correct format of name jsl

Could you apply it to my script, I don't get it pls

Re: Finding the correct format of name jsl

If you can provide your data tables I can provide better examples.

Re: Finding the correct format of name jsl

Here is the data

Re: Finding the correct format of name jsl

I think this does what you are looking for. I used Eval(Substitute()) because it is usually a bit easier to read what it is doing directly from the code. I also separated evaluation from the expression building, so you can print it if you wish to see what it looks like. For Each loops can also be debugged by setting current row to specific value as long as you have correct table as current data table.

Names Default To Here(1);

dt1 = Open("$DOWNLOADS/DATAtable1.jmp");
dt2 = Open("$DOWNLOADS/DATAtable2.jmp");
//Row() = 2;
For Each Row(
	dt2, 
	
	process_value = :Process;
	first_offset = :first_OFFSET;
	second_offset = :second_OFFSET;
	third_offset = :third_OFFSET;

	If(
		(Char(first_offset) != "." & Char(first_offset) != "") | (Char(second_offset) != "." &
		Char(second_offset) != "") | (Char(third_offset) != "." & Char(third_offset) != ""), 

		new_column_name = process_value || "_offset";
		col_expr = Substitute(
			Expr(dt1 << New Column(new_column_name, Numeric, Continuous, Formula(
				If(:Supplier == "FIRST",
					_processval_ + _firstoffset_
				, :Supplier == "SECOND",
					_processval_ + _secondtoffset_
				, :Supplier == "THIRD",
					_processval_ + _thirdoffset_
				, // else
					_processval_
				)
			))),
			Expr(_processval_), Name Expr(AsColumn(dt1, process_value)),
			Expr(_firstoffset_), first_offset,
			Expr(_secondtoffset_), second_offset,
			Expr(_thirdoffset_), third_offset
		);
		//show(col_expr);
		new_col = Eval(col_expr);
	);
);