topic Re: Begin Data Update in Discussions

Begin Data Update

Dennisbur — Mon, 06 May 2024 12:50:47 GMT

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;

Re: Begin Data Update

jthi — Mon, 06 May 2024 13:18:35 GMT

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

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;

Re: Begin Data Update

Dennisbur — Mon, 06 May 2024 14:29:20 GMT

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;

Re: Begin Data Update

jthi — Mon, 06 May 2024 14:31:24 GMT

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.

Re: Begin Data Update

martindemel — Tue, 07 May 2024 08:42:55 GMT

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.

Re: Begin Data Update

hogi — Wed, 08 May 2024 16:16:05 GMT

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:

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

Re: Begin Data Update

Dennisbur — Wed, 08 May 2024 11:09:02 GMT

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;

Re: Begin Data Update

Dennisbur — Wed, 08 May 2024 11:17:14 GMT

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;

Re: Begin Data Update

hogi — Wed, 08 May 2024 16:26:25 GMT

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!

Re: Begin Data Update

jthi — Wed, 08 May 2024 14:39:27 GMT

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)

Re: Begin Data Update

hogi — Wed, 08 May 2024 21:00:05 GMT

dt = Open("$SAMPLE_DATA/Big Class.jmp"); 
dt << Recode Column(
	/*Print(.);*/AsColumn(dt, Print(1);2),
	Print(2);{Print(3);Map Value(_rcOrig, {12, Print(4);0}, Unmatched(_rcNow))},
	/*Print(0);*/Update Properties(Print(5);1),
	/*Print(0);*/Target Column(/*Print(.);*/AsColumn(dt, Print(6);4))
);

gives

2
5
6
1
1
3
3
3
3
3
3
1
:height

Print(0): no command expected here → error message → /* I put them into comments */
Print 2: 2nd argument gets evaluated; the list stays as it is (list don't get evaluated)
Print(5) & Print (6) → arguments of the named arguments get evaluated
Print(1): now the first argument gets evaluated - why two times?
Print(3): 6 times (!) ( independent of values to replace )
Print(1): again ?

[Print(.): are evaluated, but prevent Jmp from correctly getting the Column infos → /* I put them into comments */]
Print(4): doesn't show up → not expected here → error → values are replaced by "." (instead of 0)
:height : return value: target column

Re: Begin Data Update

hogi — Wed, 08 May 2024 21:21:46 GMT

(A) In

dt = Open("$SAMPLE_DATA/Big Class.jmp");

dt[1,"age"]=4;
row()=1;
AsColumn(dt, tmp=As Column(2);print(tmp);tmp /*=4*/); // column #4 , row #1= 59
Sum(As Column(2) ,7) // 4 + 7 = 11

The result of As Column(2)is not column #2, it's the value of column #2 (in row #1).

(B) On the other hand, the first argument of Recode Column( ) gets only evaluated to a level where As Column() returns the column reference

... and NOT the value of the current row (like in the first example).

Same for the argument of the Target Column() argument.

@joseph_morgan talks in about Expressions which are "evaluated twice"
= (A)

if this "standard" is prevented (B), it feels like magic :)

Re: Begin Data Update

hogi — Thu, 09 May 2024 17:51:23 GMT

So, concerning , maybe we need a third color?