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

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

BSwid — Thu, 19 Sep 2019 21:13:18 GMT

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/td-p/10972

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

txnelson — Thu, 19 Sep 2019 21:23:50 GMT

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

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

ms — Thu, 19 Sep 2019 21:33:12 GMT

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

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

BSwid — Fri, 20 Sep 2019 12:54:47 GMT

What if the column name has a space in it?
Thank you so much!

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

pmroz — Fri, 20 Sep 2019 13:25:26 GMT

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?")

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

BSwid — Fri, 20 Sep 2019 13:33:36 GMT

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