topic Re: Properties of columns in a script in Discussions

Properties of columns in a script

Françoise — Thu, 28 Sep 2017 13:32:11 GMT

Hello,

I've written a script with the properties of each column inside:

ex.:

Open(
"S:\JUMP\JMP 2017.xlsx",
Feuilles de calcul( "JMP 2017" ),
Use for all sheets( 1 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
)
);
　
　
　
Data Table( "JMP 2017" ) << Subset( By( :Produit ),
All rows,
Selected columns only( 0 ),
columns(
:Numéro de cuve <<Modeling Type(ordinal),
:Numéro de cuve unique <<Modeling Type(ordinal),
:Date de fab <<Modeling Type(ordinal),
:Quantième <<Modeling Type(ordinal),
:Semaine <<Modeling Type(ordinal),
:Mois <<Modeling Type(ordinal),
:Année <<Modeling Type(ordinal),
:Name( "MG lait cru (g/L)") << Data Type(numeric) <<Modeling Type(continuous),
:Name( "MP lait cru (g/L)" ) << Data Type(numeric) <<Modeling Type(continuous),
:Name( "Litrage Tank 1 (L)" ) << Data Type(numeric) <<Modeling Type(continuous),
:column("Code Tank 1") << Modeling Type(ordinal),
:column("Code Tank 1 Unique") << Modeling Type(ordinal),
:Name( "MG (g/L) T1" ),
:Name( "MP (g/L) T1" ),
:Name( "EST (g/L) T1" ),
:Litrage Tank 2,
:Code Tank 2 <<Modeling Type(ordinal),
:Code Tank 2 Unique <<Modeling Type(ordinal),

But, for a new script about a new excel files, i don't remember how I've made.

I've made trials with "Show properties" without success.

Can you help me to retreive how to do it?

thanks

Re: Properties of columns in a script

ian_jmp — Fri, 29 Sep 2017 08:38:19 GMT

Pardon me if I have misunderstood what you are asking. But if you open an Excel file in JMP, the resulting table should have a 'Source' script that you can work with:

Re: Properties of columns in a script

Craige_Hales — Fri, 29 Sep 2017 17:05:52 GMT

And, interactively, right-click at the top of the column. ->show properties.

Or, use the red triangle at the top left of the table and copy the script to the clipboard.

edit: not ShowProperties. ->Column Info will tell you Data Type, Modeling Type, and let you explore some properties that might be assigned to the column. (Thanks Jeff!)

Re: Properties of columns in a script

Françoise — Mon, 02 Oct 2017 13:48:26 GMT

thanks for your answer,

I known to do it.

Please, read my answer to Craige Hales.

Best regards

Re: Properties of columns in a script

Françoise — Mon, 02 Oct 2017 13:54:19 GMT

thanks for your answer.

Ok, when I do it, I have the status of each column with the values of all the rows for each column.

I'm looking to have the script without the values because when you have a data table with 13971 rows and 97 columns, it's not easy to chnage the status of the columns with all the values in the script.

For the script in my first message, I don't have it and i don'nt remember how I've made it.

Best regards

Re: Properties of columns in a script

Craige_Hales — Mon, 02 Oct 2017 14:02:40 GMT

I think Ian has a better answer: Use the interactive Excel import on the new table. Then edit the source script in the imported table. That source script will be similar to the script you posted.

I may still be misunderstanding the question, so if this doesn't make sense, please ask again.

Re: Properties of columns in a script

Françoise — Mon, 02 Oct 2017 14:16:11 GMT

when I open the source script, Ihave only this:

Open(

"S:Pilotage\BDD New\BDD Vincent.xlsx",

Worksheets( "Qry_Cuve" ),

Use for all sheets( 1 ),

Concatenate Worksheets( 0 ),

Create Concatenation Column( 0 ),

Worksheet Settings(

Has Column Headers( 1 ),

Number of Rows in Headers( 1 ),

Headers Start on Row( 1 ),

Data Starts on Row( 2 ),

Data Starts on Column( 1 ),

Data Ends on Row( 0 ),

Data Ends on Column( 0 ),

Replicated Spanned Rows( 1 ),

Replicated Spanned Headers( 0 ),

Suppress Hidden Rows( 1 ),

Suppress Hidden Columns( 1 ),

Suppress Empty Columns( 1 ),

Treat as Hierarchy( 0 ),

Multiple Series Stack( 0 ),

Import Cell Colors( 0 ),

Limit Column Detect( 1 ),

Column Separator String( "-" )

)

But without the informations about the status of the columns( numeric, ordinal, continuous,..).

Re: Properties of columns in a script

Craige_Hales — Mon, 02 Oct 2017 15:27:40 GMT

The second half of your script runs after the table is opened. The second half of the script looks like a subset on :Produit. I think the subset probably fails because of the Modeling Type messages. But I think the Modeling Type messages do make the changes to the columns in the table.

I believe the modeling type messages were created by hand (not automatically by JMP) and (incorrectly) added in the middle of the subset command.

What I'd suggest is to keep the modeling type separate from the subset (if you need the subset.) Like this:

Open( "S:\JUMP\JMP 2017.xlsx",...  );

:Numéro de cuve <<Modeling Type(ordinal);
...
:Code Tank 2 Unique <<Modeling Type(ordinal);

Data Table( "JMP 2017" ) << Subset( By( :Produit ), columns(:Numéro de cuve,  ... :Code Tank 2 Unique) );

I think you have to determine the modeling types and assign them.

Maybe someone that has seen this before will see something I'm missing.