topic Re: Using Char to compare numeric and character values in Discussions

Using Char to compare numeric and character values

JulieSAppel — Thu, 08 Jun 2023 16:38:13 GMT

Hi,

Is it possible to compare numeric and character values?

I want to compare two columns (identical or different values). One column has a numeric date and one has a character date and as I need to compare only parts of it I want to use substring. The below script runs, but it doesn´t get me the correct result as all appears to be different (which is not the case). Am I using Char wrong?

If( Substr( Char( :Name( "Date/Time of Specimen Collection" ) ), 1, 10 ) == Substr( :Name( "Start Date/Time of Visit" ), 1, 10 ),
"identical",
"different"
)

I know I can change the format of "Date/Time of Specimen Collection" but I prefer not to as it will complicate other parts of my script.

Thanks in advance

Re: Using Char to compare numeric and character values

Craige_Hales — Tue, 07 Feb 2023 10:40:02 GMT

To understand what's happening, make a temporary column with

Char( :Name( "Date/Time of Specimen Collection" ) )

as the formula. You'll probably need to use the format(...) function (instead of char(...)) to specify the exact date-time format you need to get all the values and separator characters correct. You can see the available format names when you use the column properties dialog and look at the format...date section.

Re: Using Char to compare numeric and character values

JulieSAppel — Tue, 07 Feb 2023 11:22:53 GMT

I see your point. I cannot seem to get it right using format though.

Is there anyway of doing this without creating extra columns?

Re: Using Char to compare numeric and character values

Craige_Hales — Tue, 07 Feb 2023 11:23:39 GMT

Yes. What is the format of the character date?

Re: Using Char to compare numeric and character values

JulieSAppel — Tue, 07 Feb 2023 11:40:50 GMT

In the attached table snip I´m trying to compare the date part of "Date/Time of Specimen Collection" column (numeric, yyy-mm-ddThh:mm:ss) with the "Start Date/Time of Visit" column (character, nominal)

Is there a function that allows jmp to see the numerical values as character (formatted as a date of course) so that I can use substring for the date part of it?

Re: Using Char to compare numeric and character values

Craige_Hales — Tue, 07 Feb 2023 11:42:50 GMT

Using the format() function; the numeric date is in year-month-day for 7 February; the character format is in day/month/year.

Re: Using Char to compare numeric and character values

Craige_Hales — Tue, 07 Feb 2023 11:53:40 GMT

You might need this format:

the T format is an ISO standard.

Re: Using Char to compare numeric and character values

Craige_Hales — Tue, 07 Feb 2023 12:02:18 GMT

Or even this:

You don't need substr for the yyy-mm-dd format; it already returns just the 10 characters you need.

Re: Using Char to compare numeric and character values

JulieSAppel — Tue, 07 Feb 2023 12:58:13 GMT

Thanks a lot - I´ll give that a shot :)