Choose Language Hide Translation Bar
Highlighted
FN
FN
Level IV

Change to local format iterating through columns with date, numbers and strings

I have table I would like to format appropriately (attached as well)

FN_1-1584548219773.png

The main complexity comes from the following three problems:

  1. Users of this script will have different date formats as they will have different windows machines in different languages. For example, the database input for the column datetime is (always) in format dd-mon-yyyy hh:mm:ss which does not exist in Spanish or German languages. 

date_format.png

2. Users will also have different number formats. All the columns will result in empty numeric values as it is expecting a comma for decimals (3.14 will not be recognized as 3,14).

 

3. After the first column, which is always a date-time, columns can contain numeric or categorical values. In the attached example, column3 contains comments so there is no change of format needed.

 

Potential solutions:

 

Problem #1 (date-time using locale)

Perhaps the easier is to verify that JMP langague is in English, activate/deactivate  the option of ignoring the locale settings:

 

dt = Current Data Table();

// TODO assert that JMP language is English Preferences( Use JMP Locale Settings( 1 ) ); dt:datetime<< Set Data Type( Numeric ) << Modeling Type( continuous ) << Format( "Locale Date Time h:m:s" ); Preferences( Use JMP Locale Settings( 0 ) ); dt:datetime<< Format( "Locale Date Time h:m:s" );

Problem #2

To change the format from english to locale, we can do something similar. More info in this post

https://community.jmp.com/t5/Martin-Demel-s-Blog/JMP-and-Excel-A-Never-ending-story-Part-2/ba-p/3509...

 

dt = Current Data Table();
// TODO assert that JMP language is English Preferences( Use JMP Locale Settings( 1 ) ); dt:column1 << Set Data Type( Numeric ) << Modeling Type( continuous ); Preferences( Use JMP Locale Settings( 0 ) ); dt:column1 << Set Data Type( Numeric ) << Modeling Type( continuous );

Problem #3

How to code use the best guess from JMP so columns are only passed from characters to numeric?

 

Following this post: https://community.jmp.com/t5/Discussions/Convert-data-to-numeric-Only-If-Appropriate/td-p/106692

dt = CurrentDataTable();


// TODO Assert that JMP is in English
Preferences( Use JMP Locale Settings( 1 ) );


// Convert to numeric only if nedeed
For( i = 2, i <= N Cols( dt ), i++,
	isNumeric = "NO";
	For( theRow = 1, theRow <= 10, theRow++,
		If( Column( i )[theRow] != "" & Is Missing( Num( Column( i )[theRow] ) ) == 1,
			isNumeric = "NO";
			Break();
		,
			isNumeric = "YES"
		)
	);
	If( isNumeric == "YES",
		Column( i ) << set data type( numeric ) << Modeling Type( continuous );
		
		Preferences( Use JMP Locale Settings( 0 ) );
		Column( i ) << set data type( numeric ) << Modeling Type( continuous );
		Preferences( Use JMP Locale Settings( 1 ) );
	);
);

Preferences( Use JMP Locale Settings( 0 ) );

 

I am missing the assertion that makes sure that JMP is in English and if there is a more efficient way to this (it might have 100,000 rows and hundreds of columns).

3 REPLIES 3
Highlighted

Re: Change to local format iterating through columns with date, numbers and strings

I may not have understood the requirement fully. From what I see, all columns are character and there is no additional information that would identify a datetime column, except for its name. If that name is always the same, you don’t need to iterate through the columns, you can just name it in your script. The alternative is to write a little dialog that lets the user pick the column that shall be transformed. Also I don’t exactly get what the goal is: shall the date be displayed in the same format everywhere in the world, or shall it be displayed according to the local settings? For the moment, I assume that the respective column has always the same name and that the date format shall be displayed in local format. Then the attached script may do at least parts of what needs to be done.

Highlighted
FN
FN
Level IV

Re: Change to local format iterating through columns with date, numbers and strings

Thanks Bernd. I have updated the description of the problem and added the code that almost solves the issue.

How can I do assertions in JMP? For my solution to work, JMP needs to be in English (which should be the default in my organization).

Regards.
Highlighted

Re: Change to local format iterating through columns with date, numbers and strings

Hello,

all preferences in the "Windows" tab are NOT accessible through JSL. Therefore only workarounds are possible. One proposal, test if the current language is English, if not explain the user how to change it.

If( (Num( "2.1" ) == 2.1),
	"English",
	lw = New Window( "Warning: inappropriate formatting options",
		V List Box(
			Spacer Box( Size( 600, 20 ) ),
			H List Box(
				Spacer Box( Size( 20, 20 ) ),
				V List Box(
					Text Box( "Warning\!n", <<Set Wrap( 800 ), <<Font( "Times New Roman", 14, "Bold" ) ),
					Text Box(
						"Number formats are not consistent with English usage.\!n
Please navigate to File --> Preferences --> Windows-specific
Set \!"Display Language\!" to \!"English\!" AND 
check the box next to \!"Use JMP language rather than System locale settings,...\!n
Then re-start this program\!n",
						<<Set Wrap( 800 )
					),
					Button Box( "OK",
						lw << Close Window;
						Stop();
					)
				)
			)
		)
	)
);

 

Best regards

Bernd

 

Article Labels

    There are no labels assigned to this post.