752309_en-US

liliconcom

Begin Data Update

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;

Automation and ScriptingWindowsRe: Begin Data Update

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;

Re: Begin Data Update

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

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

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

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

 

Re: Begin Data Update

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

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

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

 

dt = Open("$SAMPLE_DATA/Big Class.jmp"); <BR />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

(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<BR />Sum(As Column(2) ,7) // 4 + 7 = 11<BR />

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 Using JSL to Develop Efficient, Robust Applications about Expressions which are "evaluated twice"
= (A)

 

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

Re: Begin Data Update

So, concerning Advanced syntax highlighting in JSL Editor - does the function evaluate it's argument? , maybe we need a third color?

Re: Begin Data Update

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;