Begin Data Update

[#1]

Hello

I have a table and would like the empty volume to be updated to 0

I have written the script to update the column "19-04-2024"

the problem is the name of the column can be changed and I prefer to use column (3) or column (4)

but it doesn't work. 

can you please correct the script?

dt << Begin Data Update;
                                    dt << Recode Column (
                                    dt << Column (3),
                                   {Map Value( _rcOrig, {., 0}, Unmatched( _rcNow ) )},
                                   Update Properties( 1 ),
                                   Target Column(Column (3) ) );
dt << End Data Update;

[#2]

AsColumn seems to work here

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt[1::10, 4] = .;

dt << Recode Column(
	AsColumn(dt, 4),
	{Map Value(_rcOrig, {., 0}, Unmatched(_rcNow))},
	Update Properties(1),
	Target Column(AsColumn(dt, 4))
);

This is simple case where I would most likely use data table subscripting Data table subscripting 

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt[1::10, 4] = .;
missing_rows = Loc(dt[0, 4], .);
dt[missing_rows, 4] = 0;

[#3]

it's something wrong

I tried to use the simple script to change empty volume to 0 in column "19-04-2024"

dt[1::10, 3] = .;
missing_rows = Loc(dt[0, 3], .);
dt[missing_rows, 3] = 0;

but I see the script change not just empty value to 0, the script change 14.3% and 21.3% to 0 too

actually my script is working well, but I don't know how I can change dt :"19-04-2024" to "column 3"

dt << Begin Data Update;
                        dt << Recode Column (dt :"19-04-2024",
                        {Map Value( _rcOrig, {., 0}, Unmatched( _rcNow ) )},
                        Update Properties( 1 ),
                        Target Column( :"19-04-2024" ) );
dt << End Data Update;

[#4]

This part

dt[1::10, 4] = .;

was there to just add some missing values for demonstration purposes (first ten rows of column 4) so you can remove it.

[#5]

Hello

I tried to change  dt :"19-04-2024" to dt [1::10, 4], but it doesn't change the empty volume to 0

can you check where I was wrong?

dt << Begin Data Update;
                        dt << Recode Column (dt [1::10, 4],
                        {Map Value( _rcOrig, {., 0}, Unmatched( _rcNow ) )},
                        Update Properties( 1 ),
                        Target Column( dt [1::10, 4] ) );
dt << End Data Update;

[#6]

You should only need this part

dt << Recode Column(
	AsColumn(dt, 4),
	{Map Value(_rcOrig, {., 0}, Unmatched(_rcNow))},
	Update Properties(1),
	Target Column(AsColumn(dt, 4))
);

where you define your column index inside AsColumn.

If I have table like this

and I wish replace missing values of column index 1 and 3, I would use

dt << Recode Column(
	As Column(dt, 1),
	{Map Value(_rcOrig, {., 0}, Unmatched(_rcNow))},
	Update Properties(1),
	Target Column(As Column(dt, 1))
);

and

dt << Recode Column(
	As Column(dt, 3),
	{Map Value(_rcOrig, {., 0}, Unmatched(_rcNow))},
	Update Properties(1),
	Target Column(As Column(dt, 3))
);

(number inside As Column has changed)

[#7]

yes, the command line

dt[1::10, 3] = .;

set all values in the rows 1 to 10 in that third column to empty. The rest of the script replaced the empty values with 0. So as @jthi explained, just remove the line.

[#8]

There are 2-3 tricks how to fix such issues:
- Name Expr(As Column(...))

- Eval(Substitute(Expr(),... , ...)))

- if there is a 3rd one, it can be found here: Expression Handling in JMP: Tipps and Trapdoors 

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
my col = Name Expr( As Column( dt, 2 ) );

Eval(
	Substitute(
			Expr(
				dt << Recode Column(
					_col_,
					{Map Value( _rcOrig, {14, 0}, Unmatched( _rcNow ) )},
					Update Properties( 1 ),
					Target Column( _col_ )
				)
			),
		Expr( _col_ ), Name Expr( my col )
	)
);

[#9]

Hello

If I understood your suggestion well, it is to change:"19-04-2024"  to your script.

but I got the error. Can you check where I was wrong?

dt << Begin Data Update;
                        dt << Recode Column (dt

                      (my col = Name Expr( As Column( Column( dt, 3 ) ) );
                       Eval( Substitute( Expr( dt << Recode Column( _col_,
                       {Map Value( _rcOrig, {14, 0}, Unmatched( _rcNow ) )},
                       Update Properties( 1 ),
                       Target Column( _col_ ))),
                       Expr( _col_ ), Name Expr( my col ))) ),
{Map Value( _rcOrig, {., 0}, Unmatched( _rcNow ) )},
Update Properties( 1 ),
Target Column( :"19-04-2024" ) );
dt << End Data Update;

[#10]

The idea is:
get a module which can be used in a flexible way, the handle is my col :

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
my col = Name Expr( As Column( XXX) );

Eval(
	Substitute(
			Expr(
				dt << Recode Column(
					_col_,
					{Map Value( _rcOrig, {14, 0}, Unmatched( _rcNow ) )},
					Update Properties( 1 ),
					Target Column( _col_ )
				)
			),
		Expr( _col_ ), Name Expr( my col )
	)
);

where  As Column(XXX) can be replaced by

• As Column (column ("sex"))
• As Column(dt, "sex")
• As Column("sex")
• As Column(3)
• As Column(dt, 3)
• in your case:
  As Column(dt, "19-04-2024")
  or  just As Column("19-04-2024")

edit:

as Jarmo shows in some post before:  https://community.jmp.com/t5/Discussions/Begin-Data-Update/m-p/752334/highlight/true#M93383 ,  for Recode Column, actually 

- one doesn't even need the Eval(Substitute(...))

- nor the Name Expr(As Column(xxx))

... Recode Column(AsColumn(xxx)...) is enough.

wow!