217000_en-US

liliconcom

Changing of column formats not working as I expect <

So I have a column that has a formula that subtracts 2 dates (two other columns.  The output is Numeric Continous.  I am trying to use

 

column(dt,"Duration")<<DATA type=""></DATA>

to change it to a duration instead of a number.  But it doesnt work

 

column(dt,"Duration")<<DATA type=""></DATA>

also doesnt work

 

I can go into the Colum info menu and change Best to Duration to hr:m:s and that works just fine

 

Re: Changing of column formats not working as I expect <

You've got the syntax for the Format() message wrong. It should be:

 

column(dt,"Duration")<<DATA type=""></DATA>

 

I figured this out by setting the format interactively in the Column Info dialog, and then I copied the Table Script to see the syntax.

 

Paste the script into a new script editor and you'll see the section that defines the Duration column:

New Column( "Duration",
  Numeric,
  "Continuous",
  Format( "hr:m:s", 13, 0 ),
  Input Format( "hr:m:s", 0 ),
  Formula( :Column 1 - :Column 2 ),
  Set Selected
 )

Re: Changing of column formats not working as I expect <

ah...I didnt understand the second part....in your post

Re: Changing of column formats not working as I expect <

That does not seem to work.

 

I created a table with 1 column "test" that had 2 rows with numbers 

and then a script for that table with the below

 

 

column("test")<<DATA type=""></DATA>

and could not change it to Duration

 

Re: Changing of column formats not working as I expect <

So I cant just change the column format to Duration with <

 

I can change the format of column with <

 

I have a post query script that creates a new column that has a formula which subtracts two other columns.  Then try to change that answer with the <

 

So you suggest that I would have to  use...with :Column 1 and :Column 2 those that have the dates that I am subtracting

 

 

column(dt,"Duration")<<DATA type=""></DATA>

 

Re: Changing of column formats not working as I expect <

I'm not sure I have enough of your particular details to know for sure how to help. So, let's back up a second to your first message:

 

---

@UberBock wrote:

So I have a column that has a formula that subtracts 2 dates (two other columns.  The output is Numeric Continous.

---

 

Where is this column with the result of the subtraction coming from? I assumed it you were doing this subtraction in JMP, but now I'm not so sure. Is this a computation that has already been done in a column in your database? If so, that may complicate things since JMP doesn't know what units the time value represents.

 

Dates and times in JMP are stored in seconds, so the :hr:m:s format will show a value of 1 as :0:0:1 and a value of 60 as :0:1:0 and a value of 3600 as :1:0:0. (You can learn more about date time values in Using dates, times, datetimes and durations in JMP.) 

 

So, let's start with that. Where is the column of the difference in the dates being computed, and what units is the result in?

Re: Changing of column formats not working as I expect <

Sure.

So I have created an SQL query that I put into 'custom SQL' to get data.  It returns a data  table that I modifiy with the post-query script.  It does a bunch of things. The returned data table has a 'completiondate' column.  Its format is like 07/03/2019 12:25:00 AM.  In one part of that post-query script I create a summary table that has a min and max from that 'completiondate' column .  I was then hoping to create another column that has a formulae which subtracts the min from the max and I wanted the format of that column to be a duration.  I tried to create the column and have it use the formulae and then format it as duration but that doesnt seem to work.  I have created other columns with formula and changed the format to percent without issue.  It seems that JMP dosent like the <

Re: Changing of column formats not working as I expect <

Thanks for the clarification. It does seem like we're on the same page.

 

The problem is you've got the syntax of the Format() message wrong. You don't need a Duration keyword in it, only the format, ":hr:m:s".

 

column(dt,"Duration")<<DATA type=""></DATA>

However, you are able to set all of these things as a part of creating the new column in the New Column() message.. In fact, the data type and modeling type should already be correct if you're creating the column with a formula. 

 

Can you show us more of the script, including the New Column() message where you create the Duration column?

 

It doesn't really matter which way you do it in terms of the end result but it will make your code a little easier to understand and slight more efficient.