Hey all, So I'm writing to see if what I'm brainstorming is possible w/ jmp. I'm starting to learn JSL for my company and slowly stumbling through this. The short story is: I end up w/ a huge ugly dt from my SQL queries and I want to create an "easy button" for myself and coworkers and managers to output a user-friendly dt. Here's what I'm thinking. I think I have a good idea about how to do most of it except for the dialogue pop ups and selecting. Given a dt, can a dialogue pop up be scripted to allow user to select (checkbox, radials?) column names? Can these have an empty text box next to them to allow users to rename them? Adam's SQL dt Easy Button special: Step: * Popup asking for path & file * Delete empty columns * Delete duplicate rows * Remove duplicate columns (dialogue box to select duplicates?) * there's many columns that are duplicates, just slightly different versions. * multiple columns for time stamps * multiple columns for variations of lot # names or slot/wafer #'s Remove unnecessary columns (dialogue box to ask what to remove)* a lot of columns are unnecessary or non-relevant to data analysis adjust timestamp format to easy readabilityRename columns, ex: "Y1_SPC_CTRL_SPEC_HIGH" --> USL (dialogue box to select col names and then empty text box to type in new name?)Reorganize column order, place 'like' items together (column grouping?)Concatenate columns, ex: Tool_Ch_Side2-sample label: Group 1 vs Group2, "DOE vs POR", (dialogue box to select one/two/three unique values in a column and all else = "por"

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL dt needs reformat --> many steps to get user friendly dt, advice?

saspecvd

Hey all,

So I'm writing to see if what I'm brainstorming is possible w/ jmp. I'm starting to learn JSL for my company and slowly stumbling through this. The short story is: I end up w/ a huge ugly dt from my SQL queries and I want to create an "easy button" for myself and coworkers and managers to output a user-friendly dt. Here's what I'm thinking.

I think I have a good idea about how to do most of it except for the dialogue pop ups and selecting. Given a dt, can a dialogue pop up be scripted to allow user to select (checkbox, radials?) column names? Can these have an empty text box next to them to allow users to rename them?

Adam's SQL dt Easy Button special:

Step:

Popup asking for path & file
Delete empty columns
Delete duplicate rows
Remove duplicate columns (dialogue box to select duplicates?)

there's many columns that are duplicates, just slightly different versions.
multiple columns for time stamps
multiple columns for variations of lot # names or slot/wafer #'s

Remove unnecessary columns (dialogue box to ask what to remove)

a lot of columns are unnecessary or non-relevant to data analysis

adjust timestamp format to easy readability

Rename columns, ex: "Y1_SPC_CTRL_SPEC_HIGH" --> USL (dialogue box to select col names and then empty text box to type in new name?)

Reorganize column order, place 'like' items together (column grouping?)

Concatenate columns, ex: Tool_Ch_Side

2-sample label: Group 1 vs Group2, "DOE vs POR", (dialogue box to select one/two/three unique values in a column and all else = "por"

Accepted answers

Mark_Bailey

It sounds to me like you are "re-inventing the wheel." I suggest that you first study the Query Builder. Select Help > Books > Using JMP. See chapter 3, Import Your Data. You might need to provide some custom scripting, but I think you can avoid a lot of unnecessary work starting with a powerful tool like the Query Builder.

The strategy is based on making the database do the 'heavy lifting' for which it was designed and deliver the desired datatable to JMP they way that you want it.

All comments

Mark_Bailey

The strategy is based on making the database do the 'heavy lifting' for which it was designed and deliver the desired datatable to JMP they way that you want it.

saspecvd

I very much agree....I'm very familiar w/ formating the output dt on the query building side of things and I wish i could do this directly, however my company won't actually give us direct RO access to the database. We have to use this clunky, proprietary 'middle-man' program that prebuilds the SQL query and we can't change it....it's so frustrating. We can't even adjust what columns are used for filters. For example, querying wafer thickness. You can't even choose to filter by production tool, despite the production tool being one of the output columns.

I'm trying to make lemonade here. I'm currently working on getting the database managers to open up access, but until then, we are all stuck with this steaming pile of crap....

so I am looking to try to format the dt on the backend, rather than w/ in the original query.

art

I have only used query builder on SQL databases, but I suspected and SAS confirmed, that you can use query builder on your data table itself. This should get you most of the way there with filtering/grouping/managing columns. Managing rows will require custom JSL script, but will get you most of the way there.

13 reasons data access is better than ever in JMP 13