Just when I thought I understood Eval and Expr...

[#1]

I've been working on this problem for two days now reviewing entries in this discussion group, going through the scripting guide, etc. but I still can figure out what is wrong.

I am trying to use a variable to reference a column. From script rows 65-75 (right before For Each Row), I am trying all sorts of ways to use Expr and Eval and Name Expr and As Column, etc. to make this work. The current script in shows just the most recent iteration.

Depending on the value of aggOption, I want the Agg Minute (Option 1) column to fill in one of three different ways. If you substitute the actual column name :"Agg Minute (Option 1)"n in place of Eval( colName3), it works as intended. However, no matter what combination of Eval, Expr, etc I use in either defining the variable or evaluating the variable, I either get  the following error message...

... OR the script executes without error but the column is not filled in.

So it seems the left side of the assignment is not being interpreted as a column name.

I'm sure this must be some simple misunderstanding on my part as to either how Eval and Expr works or how the Choose function works. But I can't seem to figure out what it is.

Does anyone have insight into what I'm doing wrong?

Thanks as always.

Names Default To Here( 1 );

// SETUP: Create Raw Data Table
Try( Close( "Interpolated Data"));
Try( Close( "Test rt"));

rt = New Table( "Test rt",
	Add Rows( 5 ),
	New Column( "Timestamp", Continuous, Format( "m/d/y h:m", 19 ), set values( [3742070400, 3742070880, 3742071360, 3742071840, 3742072320] ) ),
	New Column( "Data", set values( [899.084, 897.387, 898.518, 897.575, 897.387] ) )
);

t1 = column( rt,1) << Get Name;
t2 = column( rt,2) << Get Name; 

timeCol = Parse(":" || t1);
dataCol = Parse(":" || t2);

Print( timeCol);
Print( dataCol);

rtRows = N Rows( rt );
startTime = rt:Timestamp[1];
endTime = rt:Timestamp[rtRows];
minDuration = (endTime - startTime) / 60; 


// CREATE SECONDARY TABLE
dt = New Table( "Interpolated Data Temp" );
dt << New Column( "Timestamp", Format( "m/d/y h:m", 19 ), Set Display Width( 250 ) );
dt << Add Rows( minDuration ); 

For Each Row( dt, :Timestamp[] = Sequence( startTime, endTime, 60 ) );


// JOIN BOTH TABLES
dt << Join(
	With( rt ),
	Suppress formula evaluation( 0 ),
	Select( :Timestamp ),
	SelectWith( Name Expr(dataCol)),
	By Matching Columns( :Timestamp = :Timestamp ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 0 ),
	Preserve main table order( 1 ),
	Output Table( "Interpolated Data" )
);

dt2 = Data Table( "Interpolated Data" );

Close( dt, NoSave );
Close( rt, NoSave );


// CREATE AGG MINUTE/AGG QUADRANT COLUMNs
// These are  the columns that will define which minutes are included in each quadrant and how the quadrant will be labled.
// The choice of how the columns will be filled depends on the Option chosen above.
// **************************************************************************

aggOption = 1;
colName1 = "Agg Minute (Option " || char(aggOption) ||  ")";
colName2 = "Agg Quadrant (Option " || char(aggOption) || ")";


Print( colName1);
Print( ColName2);

colName3 = Name Expr(":" || colName1);
colName4 = Name Expr(":" || colName2);

Print( colName3);
Print( colName4);

New Column(colName1, Numeric, "Ordinal", Format("Best", 12), Set Display Width(116)); // To Do: Add chosen agg option to column name
New Column(colName2, Numeric, "Ordinal", Format("Best", 12), Set Display Width(116)); // To Do: Add chosen agg option to column name


For Each Row(dt2,
	 Eval(colName3) = Choose(aggOption, // Substituting an actual column name (:"Agg Minute (Option 1)"n works)
		If(
			Minute(:Timestamp) < 15, 15, // Option 1
			Minute(:Timestamp) < 30, 30,
			Minute(:Timestamp) < 45, 45,
			0
		),
		If(
			Minute(:Timestamp) < 15, 0, // Option 2
			Minute(:Timestamp) < 30, 15,
			Minute(:Timestamp) < 45, 30,
			45
		),
		If(
			0 < Minute(:Timestamp) <= 15, 15, // Option 3
			15 < Minute(:Timestamp) <= 30, 30,
			30 < Minute(:Timestamp) <= 45, 45,
			0
		)
	)
);

[#2 · Accepted solution]

There is almost never need to start messing with ":" (adding ":" as a string) and even less with Parse(). When you are setting values to a column with For Each Row you can do something like this for example

For Each Row(
	dt2,
	Column(dt2, colName1)[Row()] = Choose(aggOption, // Substituting an actual column name (:"Agg Minute (Option 1)"n works)
		If(
			Minute(:Timestamp) < 15, 15, // Option 1
			Minute(:Timestamp) < 30, 30,
			Minute(:Timestamp) < 45, 45,
			0
		),
		If(
			Minute(:Timestamp) < 15, 0, // Option 2
			Minute(:Timestamp) < 30, 15,
			Minute(:Timestamp) < 45, 30,
			45
		),
		If(
			0 < Minute(:Timestamp) <= 15, 15, // Option 3
			15 < Minute(:Timestamp) <= 30, 30,
			30 < Minute(:Timestamp) <= 45, 45,
			0
		)
	)
);

This isn't the only option but it is very simple. If you really want to start messing with expressions, this is one way

Eval(EvalExpr(
	For Each Row(
		dt2,
		Expr(Name Expr(AsColumn(dt2, colName1))) = Choose(aggOption, // Substituting an actual column name (:"Agg Minute (Option 1)"n works)
			If(
				Minute(:Timestamp) < 15, 15, // Option 1
				Minute(:Timestamp) < 30, 30,
				Minute(:Timestamp) < 45, 45,
				0
			),
			If(
				Minute(:Timestamp) < 15, 0, // Option 2
				Minute(:Timestamp) < 30, 15,
				Minute(:Timestamp) < 45, 30,
				45
			),
			If(
				0 < Minute(:Timestamp) <= 15, 15, // Option 3
				15 < Minute(:Timestamp) <= 30, 30,
				30 < Minute(:Timestamp) <= 45, 45,
				0
			)
		)
	);	
));

One nice thing with EvalExpr (same deal with Substitute) is that you can easily see the expression which will be evaluated if Eval is used, but selecting the EvalExpr() part and then running that

[#3]

Thanks, Jarmo.

I did try options with parse and with and without the leading colon to no avail. However, I did not try the combinations you showed above. It makes perfect sense once you see it done.

[#4]

It's interesting that you need the Row() function here when it is not necessary if using the actual name of the column. I would not have guessed that.

[#5]

That is just my preferred method as use Column(dt, colname) whenever I can (you can also drop Row() and just use Column()[]). I think should also work with As Column()

For Each Row(
	dt2,
	As Column(dt2, colName1) = Choose(aggOption, // Substituting an actual column name (:"Agg Minute (Option 1)"n works)
		If(
			Minute(:Timestamp) < 15, 15, // Option 1
			Minute(:Timestamp) < 30, 30,
			Minute(:Timestamp) < 45, 45,
			0
		),
		If(
			Minute(:Timestamp) < 15, 0, // Option 2
			Minute(:Timestamp) < 30, 15,
			Minute(:Timestamp) < 45, 30,
			45
		),
		If(
			0 < Minute(:Timestamp) <= 15, 15, // Option 3
			15 < Minute(:Timestamp) <= 30, 30,
			30 < Minute(:Timestamp) <= 45, 45,
			0
		)
	)
);

but in my opinion As Column() is much more annoying to use so I don't use that much... Except when I'm using it with Name Expr() and I'm trying to build something which requires you to have :col format or it just cleaner to have column in that format (like in formulas).