topic Re: output data table query from database is all categorical (numerical columns changed to categorical) in Discussions

output data table query from database is all categorical (numerical columns changed to categorical)

dadawasozo — Sat, 10 Jun 2023 23:30:57 GMT

Hi,

I 'm using the query builder to get data from database. The the included columns from table are a mixed of categorical and numerical type. After I clicked Run Query, a table appears and contains all the columns I needed. However , all the columns appear in categorical type. How I can make sure not losing the data type when query data using jmp?

Re: output data table query from database is all categorical (numerical columns changed to categorical)

Craige_Hales — Sat, 29 May 2021 02:16:46 GMT

Here

Right click the icons to get the popup.

Re: output data table query from database is all categorical (numerical columns changed to categorical)

dadawasozo — Sat, 29 May 2021 02:47:14 GMT

Yes, under the included columns window I make sure the data type is continuous. However, the table generated after I clicked Run Query change all those continuous to categorical. Is this a bug?

Re: output data table query from database is all categorical (numerical columns changed to categorical)

Craige_Hales — Sat, 29 May 2021 02:57:41 GMT

I'm not an expert on query builder, but the simple example worked for me in JMP 16.

It might make a difference if the database columns are character rather than numeric.

What version of JMP?

Add a picture might help too.

Re: output data table query from database is all categorical (numerical columns changed to categorical)

dadawasozo — Sat, 29 May 2021 03:13:18 GMT

I m using jmp pro 14

what I saying is:
1) when doing query, the continuous-type columns is there (see left of image last row)
2) after clicked Run Query, the table generate shows the previous continuous-type column become categorical (see right side of image)
3) Thus, I wonder if there is bug for jmp pro 14 query builder ?
4) or, I missed some setting?

Re: output data table query from database is all categorical (numerical columns changed to categorical)

Craige_Hales — Sat, 29 May 2021 04:28:46 GMT

14.3 works as I expect, the continuous default is not changed. I'm out of suggestions, except to look at the source script in the opened table; you might be able to change to "Continuous" (or delete) the highlighted bits and get what you need.

New SQL Query(
Connection(
"ODBC:DSN=CSV;DBQ=C:\SPACE\SAS\JMP\15\SAMPLES\IMPORT DATA;DefaultDir=C:\SPACE\SAS\JMP\15\SAMPLES\IMPORT DATA;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;"
),
QueryName( "Big Class.csv" ),
Select(
Column( "name", "t1" ),
Column( "age", "t1" ),
Column( "sex", "t1" ),
Column( "height", "t1", Analysis Type( "Ordinal" ) ),
Column( "weight", "t1", Analysis Type( "Nominal" ) )
),
From( Table( "Big Class.csv", Alias( "t1" ) ) )
) << Run

Beyond that, I'd suggest tech support. (Might be good anyway; it does sound like a bug.)

Re: output data table query from database is all categorical (numerical columns changed to categorical)

dadawasozo — Sat, 29 May 2021 15:15:03 GMT

Hi ,

I think there is definitely a bug.
I tried
Column( "height", "t1", Analysis Type( "Ordinal" ) ), #output table is Ordinal
However, when I do
Column( "height", "t1", Analysis Type( "Continuous" ) ), # output table is nominal

So, that continuous data type property cannot be queried out. I need to add step on downstream where I will need to change the data type back to continuous after table queried.