BookmarkSubscribe
Choose Language Hide Translation Bar
BSwid
Community Trekker

Script to add leading characters (leading zeros in this case)

What's the syntax to have a column operate on itself?

This is the best I could come up with and it doesn't work.

 

dt = current data table();
column(dt, "Architecture") << Set Each Value( Repeat("0",3-Length(Char("Architecture")))||Char("Architecture"));

 

Column Architecture has values like 1, 2, 123, XXX, etc.  Length is not standard.

Want a script that will run on the table and covert the column to standardize the length at 3 and add any leading zeros.

Resulting values would be, 001, 002, 123, XXX, etc.

 

Saw this post and it was almost there.
https://community.jmp.com/t5/Discussions/How-to-represent-numeric-value-quot-1-quot-as-quot-001-quot...

 

 

 

0 Kudos
3 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: Script to add leading characters (leading zeros in this case)

You need to loop through each row an apply the change:

Names Default To Here( 1 );
dt = Current Data Table();
For Each Row(
	dt:Architecture = Repeat( "0", 3 - Length( Char( dt:Architecture ) ) ) ||
	Char( dt:Architecture )
);
Jim
ms
Super User ms
Super User

Re: Script to add leading characters (leading zeros in this case)

You're almost there. Use the scoping character (single colon) to make sure JSL understands it's a variable (i.e. a column), and not a string.

 

dt = Current Data Table();
dt:Architecture << Set Each Value(Repeat("0", 3 - Length(dt:Architecture)) || dt:Architecture);
pmroz
Super User

Re: Script to add leading characters (leading zeros in this case)

JSL can forgive spaces in column names.  But it can't forgive special characters.  The following statements are equivalent references to a column.

dt:Architecture Digest
column(dt, "Architecture Digest")

 For special characters you can use column() or :Name()

// Column with special characters, ABC=DEF?
column(dt, "ABC=DEF?")
:Name("ABC=DEF?")
5 REPLIES 5
txnelson
Super User

Re: Script to add leading characters (leading zeros in this case)

You need to loop through each row an apply the change:

Names Default To Here( 1 );
dt = Current Data Table();
For Each Row(
	dt:Architecture = Repeat( "0", 3 - Length( Char( dt:Architecture ) ) ) ||
	Char( dt:Architecture )
);
Jim
ms
Super User ms
Super User

Re: Script to add leading characters (leading zeros in this case)

You're almost there. Use the scoping character (single colon) to make sure JSL understands it's a variable (i.e. a column), and not a string.

 

dt = Current Data Table();
dt:Architecture << Set Each Value(Repeat("0", 3 - Length(dt:Architecture)) || dt:Architecture);
BSwid
Community Trekker

Re: Script to add leading characters (leading zeros in this case)

What if the column name has a space in it?
Thank you so much!
0 Kudos
pmroz
Super User

Re: Script to add leading characters (leading zeros in this case)

JSL can forgive spaces in column names.  But it can't forgive special characters.  The following statements are equivalent references to a column.

dt:Architecture Digest
column(dt, "Architecture Digest")

 For special characters you can use column() or :Name()

// Column with special characters, ABC=DEF?
column(dt, "ABC=DEF?")
:Name("ABC=DEF?")
Highlighted
BSwid
Community Trekker

Re: Script to add leading characters (leading zeros in this case)

Thank you both.  The software is great and the user community is even better!