topic Re: Finding the correct format of name jsl in Discussions

Finding the correct format of name jsl

PercentileBat71 — Thu, 08 Aug 2024 13:02:37 GMT

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 :(

Re: Finding the correct format of name jsl

jthi — Thu, 08 Aug 2024 13:05:16 GMT

Build the formulas using expression evaluation . 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

PercentileBat71 — Thu, 08 Aug 2024 13:27:08 GMT

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

jthi — Thu, 08 Aug 2024 13:28:59 GMT

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

PercentileBat71 — Thu, 08 Aug 2024 13:33:16 GMT

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

jthi — Thu, 08 Aug 2024 13:35:11 GMT

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

jthi — Thu, 08 Aug 2024 13:41:16 GMT

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

PercentileBat71 — Thu, 08 Aug 2024 13:42:25 GMT

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

Re: Finding the correct format of name jsl

jthi — Thu, 08 Aug 2024 13:44:47 GMT

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

Re: Finding the correct format of name jsl

PercentileBat71 — Thu, 08 Aug 2024 14:12:31 GMT

Here is the data

Re: Finding the correct format of name jsl

jthi — Thu, 08 Aug 2024 14:35:21 GMT

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