Scripting: Retrieving Column certain names of a data table<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />Hi,
I have a data table (imported from Excel) where some column names have a date Format (e.g. September 14). But there are other columns as well. Attached you find the jmp data table Example1. There are two issues.
First Issue:
I need to stack the date columns to have them e.g. as X in a Graph Builder analysis.This is not very difficult (script in Example1, but the table will be a living document and new columns will be added as months progress and therefore I cannot use the automated script with hard coded columns.
I need to extract those column names with such a date structure (Month YY) and stack them. I tried to use Get Column Name which extracts the column list:
colNames = Data Table( "Example1" ) << Get Column Names;
show(colNames);
But I could not extract just the ones I'm interested to stack. I thought about Regex() but couldn't get it run in the way I liked it to have.
Second Issue:
Then the next issue appears. The data type will be character and nominal. I would like it to be numeric and ordinal (or at least character and ordinal but in the correct series). When changing the property to numeric I got a lot of missing values, but not all.
Not sure what I do wrong here.
Thanks for your help,
Martin
Re: Scripting: Retrieving Column certain names of a data tableHi Don,
I now understand the regexp formula better you have used. I think I can adjust it to a General purpose by adding conditions, e.g. in case there is a mixture between variable names with "Month YY", "DD Month YY", "DD.MM.YY" and so forth and adjust it to the final Format.
The best would be to have a consistent Input Format for the variable names which JMP knows.
Thanks for your help, you got me on the right track with your example. Thanks!
Martin
Re: Scripting: Retrieving Column certain names of a data tableHi Don,
I came arround the date Transfer by using an if construct as the word Count depends if there is a leading number or not:
Current Data Table() << New Column( "New Date",
Numeric,
Ordinal,
Format( "ddMonYYYY" ),
Formula(
If( Is Missing( Num( Word( 1, :Datum ) ) ),
Date DMY(
1,
Match( Word( 1, :Datum ),
"Januar", 1,
"Februar", 2,
"März", 3,
"April", 4,
"Mai", 5,
"Juni", 6,
"Juli", 7,
"August", 8,
"September", 9,
"Oktober", 10,
"November", 11,
"Dezember", 12,
13
),
2000 + Num( Word( 2, :Datum ) )
),
Date DMY(
Num( Word( 1, :Datum ) ),
Match( Word( 2, :Datum ),
"Januar", 1,
"Februar", 2,
"März", 3,
"April", 4,
"Mai", 5,
"Juni", 6,
"Juli", 7,
"August", 8,
"September", 9,
"Oktober", 10,
"November", 11,
"Dezember", 12,
13
),
2000 + Num( Word( 3, :Datum ) )
)
)
)
);
But I still have issues regarding the regexp formula to create the list regNames.
Martin
Re: Scripting: Retrieving Column certain names of a data tableHi Don,
I tried to work through your example. However, I get an empty regNames list and therefore the stack script prompts me to enter all as previously. This results into a data table where the script for the New Column would not work because of a missing list. In addition the Date Transformation does not work either for me.
Hae you tried and did it work for you? Which Settings for the language did you use?
Thanks,
Martin
Re: Scripting: Retrieving Column certain names of a data tableMartin,
I'm attaching a script that should solve the issues you're having. Regex is particularly tricky to use in JSL and varies somewhat from standard regular expressions (although, it's pretty close, mostly missing some functionality). The date issues are occurring because JMP did not recognize the dates as an available informat, at least I didn't see them on my version. My solution parsed the name and created a new column with numeric dates. There are alternative solutions, although I though this would be the easiest. !
