Finding the correct format of name jsl

[#1]

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

[#2 · Accepted solution]

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;

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

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

[#8]

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

[#9]

Here is the data

[#10]

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