topic Re: Recode a value in a column with a formula instead of a static value in Discussions

Recode a value in a column with a formula instead of a static value

Phil_Nash — Fri, 09 Jun 2023 16:04:48 GMT

The original value that is in the table that I want to recode will always be 01/01/1900. I want to recode that to today's date. This only returns a blank, and not today's date. Any idea what I need to change?

Thanks!

Transfers << Begin Data Update;
Transfers << Recode Column(
	Transfers:OrderDate,
	{Map Value( _rcOrig, {-126144000, short date(today())}, Unmatched( _rcNow ) )},
	Update Properties( 1 ),
	Target Column( :OrderDate )
);
Transfers << End Data Update;

Re: Recode a value in a column with a formula instead of a static value

Mark_Bailey — Tue, 13 Dec 2022 20:22:11 GMT

I am not sure that the source column and the destination column can be the same data column. Here is the example from the scripting index:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
col = New Column( :age );
col << Data Type( "Character" );
dt << Recode Column(
	:age,
	{If(
		_rcNow >= 17, "Older",
		_rcNow >= 15, "Middle",
		"Younger"
	)},
	Target Column( col )
);

Re: Recode a value in a column with a formula instead of a static value

hogi — Tue, 07 Oct 2025 20:24:47 GMT

To recode entries in a column and save the result to the same column, one can select In Place from the drop down menu:

In the JSL code the target column will match the input column.

Hm, Why does the code not work?

a) Short Date() converts the date to a String - but the column is numeric.

b) even after removing short date() it doesn't work. Because Map value() doesn't allow expressions? maybe:

So let's try:

Transfers = New Table( "transfers",
	Add Rows( 1 ),
	Compress File When Saved( 1 ),
	New Column( "OrderDate",
		Format( "y/m/d"),
		Set Values( [-126144000] )
	)
);

Transfers << Begin Data Update;
Eval (Eval Expr(Transfers << Recode Column(
	Transfers:OrderDate,
	{Map Value( _rcOrig, {-126144000, Expr(today())}, Unmatched( _rcNow ) )},
	Update Properties( 1 ),
	Target Column( :OrderDate )
)));
Transfers << End Data Update;