Combining Date Columns in Query BuilderI 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
Re: Combining Date Columns in Query BuilderYup, they post query script method is the most flexible and can be done before the Graph Builder part.
Re: Combining Date Columns in Query BuilderThanks Chris,
I did look at that option but did not see a way to do it as easily as can be done with JSL.
Re: Combining Date Columns in Query BuilderSupplementing @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.
Re: Combining Date Columns in Query BuilderHi 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.
Re: Combining Date Columns in Query BuilderI'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(...);
Re: Combining Date Columns in Query BuilderYou could create it as a virtual column in graph builder. I have done something similar for a dashboard builder / query builder demo.
Phil
