topic Expr and Name Expr in Discussions

Expr and Name Expr

Jackie_ — Thu, 09 Nov 2023 14:35:54 GMT

Hello,

I am analyzing larger amounts of data, 350k rows.

For each row, the code runs a loop to calculate the unique delta between table 1 and table 2 and then add that delta to the column of the main data table. Something is not correct in the below code. Any suggestion?

Names Default To Here( 1 );
dt1 = Data Table( "dt_ref" );
dt2 = Data Table( "Data table" );

digt = 5;
itsel1 = {"Currents A", "Currents A2", "Currents A3", "Currents A4", "Currents A5"}; //List of columns to align.

//// Loop to calculate the delta between the dt1 and dt2 and the add the delta to the column of dt2 from the itsel1 list
dt2 << Add Row( 1, At Start );
dt2 << Begin Data Update;

For Each( {element, idx}, itsel1,
	Eval(
		Eval Expr(
			Column( dt2, element ) << Set Each Value(
				Expr(
					Name Expr( Column( dt2, element ) )
				),
				Expr(
					Num(
						Substr( Char( Column( dt1, "Median" )[dt1 << get rows where( :Tests == element )] - Column( dt2, element ) ), 1, digt + 1 )
					) + Column( dt2, element )
				)
			)
		)
	)
);


dt2 << Delete Row( 1 );
dt2 << End Data Update;

Thanks,

Jackie

Re: Expr and Name Expr

jthi — Thu, 09 Nov 2023 15:47:53 GMT

I'm not sure what type of calculation you are performing here

Num(Substr(Char(Column(dt1, "Median")[dt1 << get rows where(:Tests == element)] - Column(dt2, element)), 1, digt + 1)) + Column(dt2, element)

but something like this might help you

Names Default To Here(1);
dt1 = Open("$DOWNLOADS/dt_ref.jmp");
dt2 = Open("$DOWNLOADS/Data table.jmp");

digt = 5;
itsel1 = {"Currents A", "Currents A2", "Currents A3", "Currents A4", "Currents A5"}; //List of columns to align.
dt2_colnames = dt2 << Get Column Names("String");
//// Loop to calculate the delta between the dt1 and dt2 and the add the delta to the column of dt2 from the itsel1 list

For Each({colname}, itsel1,
	dt2_col_idx = Contains(dt2_colnames, colname);
	If(dt2_col_idx == 0,
		Print("Skipped column: " || colname);
		continue(); // skip this column
	);
	
	dt1_row_idx = Loc(dt1[0, "Tests"], colname);
	If(N Items(dt1_row_idx) == 0,
		Print("Skipped row: " || colname);
		continue();
	,
		dt1_row_idx = dt1_row_idx[1]; 
	);
	
	dt2[0, dt2_col_idx] = dt2[0, dt2_col_idx] - dt1[dt1_row_idx, "Median"];
);

Re: Expr and Name Expr

Jackie_ — Thu, 09 Nov 2023 16:00:27 GMT

So I am trying to align the data in dt2 with the median values of Test in dt1

So The median value of Current A1 is 5.408 so subtract each rows of Current A1 in dt2 and add that back to the row values of Current A1 in dt2
5.408 - 2.4963333333 = 2.912

2.912 + 2.496333333 = 5.40. I avoiding traditional for loops bcos I have a huge dataset with 1M rows and Expr would be faster

Re: Expr and Name Expr

jthi — Sun, 12 Nov 2023 14:34:34 GMT

Wouldn't you just end up with the median value if you don't round your values?

x = median,

y = row value,

z = result

x - y = z

z + y = x?

Re: Expr and Name Expr

Jackie_ — Mon, 13 Nov 2023 18:18:12 GMT

@jthi That's correct but I want to round off "z" to 2 decimals and then add it back to y. The reason for doing this is "offsetting the tester data with the master data"