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; } } }

Combining Date Columns in Query Builder

Bill_Worley

I have a customer who is using Query Builder to pull data in. Their date data is stored in their database in 3 separate columns. Month, Day, and Year. The customer would like to combine these columns into an M/D/Y format in Query Builder at some point in the query process, but definitely before the final table is built.

I have shown him how to combine these columns in a JMP table, but his goal is to output a graph using post-query scripting with the M/D/Y format for his x-axis.

Is there a way to do this with SQL or would it be best to add a JSL script for building the M/D/Y column before the script fo the graph builder step in the post-query script?

BTW - I tried creating a new formula column in Query Builder, but I did not see a way to combine the columns as desired.

Thanks,

Bill

Accepted answers

Nate_Riordan

Supplementing @MikeD_Anderson

Indeed adding the column via post query JSL is easy enough. Although to leverage date time format it is required to define the column as Numeric. The values for the date column can be dynamic by leveraging "Formula" as in the code below, or static by using "Set Each Value"

current data table() << New Column( "MDY_Col", Numeric, Continuous, Formula( Date MDY(:month, :day, :year)) , Format("m/d/y"));

Query builder does let you build computed columns as @Chris_Kirchberg mentions. Although you're limited to SQL commands.

All comments

Phil_Kay

You could create it as a virtual column in graph builder. I have done something similar for a dashboard builder / query builder demo.
Phil

MikeD_Anderson

I've had really good luck by using JSL in the post query script to generate the combined column before running the graph.

So the JSL would be something like this:

New Column( "NewCol1", Character, "Nominal", Formula( Date DMY(:day, :month, :year));

Graph Builder(...);

Chris_Kirchberg

Hi Bill,
Are they using JMP 13? If so, then one can make a computed column based on what available columns are there. Take a look at the red triangle next to the Available Columns section. That may help.

Nate_Riordan

Supplementing @MikeD_Anderson

current data table() << New Column( "MDY_Col", Numeric, Continuous, Formula( Date MDY(:month, :day, :year)) , Format("m/d/y"));

Query builder does let you build computed columns as @Chris_Kirchberg mentions. Although you're limited to SQL commands.

Bill_Worley

Thanks Chris,

I did look at that option but did not see a way to do it as easily as can be done with JSL.

Chris_Kirchberg

Yup, they post query script method is the most flexible and can be done before the Graph Builder part.