cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ileshem
Level III

How do I Create a Column using "IF" and "Contains"

Hello,

 

I have a date and time column with "AM" and "PM" in the format.

Using JSL, I wand to add a new column that will indicate it its a day or night.

Row with "PM" in the date will input "Day"

Row with "AM" in the date will input "Night"

 

I try using the following: 

 

For( i = 1, i <= N Rows( dt ), i++,
If( Contains( :Date[i], "PM" ) ,
:Day/Night[i] = :Day[i]
)

 

But it wont work.

Thanks in advance!
);

1 ACCEPTED SOLUTION

Accepted Solutions
julian
Community Manager Community Manager

Re: How do I Create a Column using "IF" and "Contains"

Hi @ileshem,

If your Date column is stored in proper date/time format, it won't actually contain characters, even though it's displayed as such. So, you'll need a function that doesn't look for the AM/PM, but rather can determine if the DateTime indicates a time after 12pm or not. I've attached a table in which I've done this using the following column formula:

If( Hour( :Time ) >= 12, "Night", "Day")

You could adapt this to work as part of your script easily, like below:

For( i = 1, i <= N Rows( dt ), i++, 
	If( Hour( :Time[i] ) >= 12,
		:Name( "Day/Night" )[i] = "Night",
		:Name( "Day/Night" )[i] = "Day"
	)
);

These use the Hour() function to pull the hour of the day from the DateTime. Also, in your original JSL, you need to :Name() to reference your column Day/Night, since the special character (a slash) means the column name needs to be quoted.

 

I hope this helps!

 

@julian 

 

View solution in original post

3 REPLIES 3
julian
Community Manager Community Manager

Re: How do I Create a Column using "IF" and "Contains"

Hi @ileshem,

If your Date column is stored in proper date/time format, it won't actually contain characters, even though it's displayed as such. So, you'll need a function that doesn't look for the AM/PM, but rather can determine if the DateTime indicates a time after 12pm or not. I've attached a table in which I've done this using the following column formula:

If( Hour( :Time ) >= 12, "Night", "Day")

You could adapt this to work as part of your script easily, like below:

For( i = 1, i <= N Rows( dt ), i++, 
	If( Hour( :Time[i] ) >= 12,
		:Name( "Day/Night" )[i] = "Night",
		:Name( "Day/Night" )[i] = "Day"
	)
);

These use the Hour() function to pull the hour of the day from the DateTime. Also, in your original JSL, you need to :Name() to reference your column Day/Night, since the special character (a slash) means the column name needs to be quoted.

 

I hope this helps!

 

@julian 

 

ileshem
Level III

Re: How do I Create a Column using "IF" and "Contains"

WOW!

Thanks!

txnelson
Super User

Re: How do I Create a Column using "IF" and "Contains"

@ileshem ,

All of this syntax is nicely documented in the Scripting Guide.  I strongly suggest that you take the time to read this documntation.

     Help==>Books==>Scripting Guide

It will give you the detail you need to make your venturing into JSL much easier.

Jim