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

[#1]

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!
);

[#2 · Accepted solution]

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 

[#3]

WOW!

Thanks!

[#4]

@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.