Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to load a date value in a script based on whether one date value is null or not. The one date value is in a delimiter string. It is loaded with the following command in the script.
subfield("char_fld",';',5) AS so_user_Promise_date;
The other date field is always populate. It is loaded int he script as follows:
"ship_date" AS so_ship_date,
I the use the following load function to create a date field with either so_user_Promise_date or so_ship_date:
Load *,
schedule_qty*so_unit_price AS so_total,
so_open_qty*so_unit_price AS so_total_open,
If(Len(Trim([so_user_Promise_date]))=0,so_ship_date,so_user_Promise_date) AS so_promise_date
RESIDENT sols;
drop table sols;
The issue is so_promise_date display correctly if so_user_Promise_date is loaded. If so_ship_date is loaded I get the numeric value of the date. Example 41390. I have tried MakeDate, Date# with no luck. How can I get so_promise_date to always display the date?
so_ship_date is a correct date, so_user_Promise_date is a string. You should use the date# function on so_user_Promise_date to make a date of it. Once you've done that you can format so_promise_date in charts so it shows in a date format. If you want you can also do that in the script
date(If(Len(Trim([so_user_Promise_date]))=0,so_ship_date,date#(so_user_Promise_date,'DD-MM-YYYY')),'DD-MM-YYYY') AS so_promise_date
change the first DD-MM-YYYY with the date format that the string field so_user_Promise_date has. Change the second to however you want your dates displayed.
so_ship_date is a correct date, so_user_Promise_date is a string. You should use the date# function on so_user_Promise_date to make a date of it. Once you've done that you can format so_promise_date in charts so it shows in a date format. If you want you can also do that in the script
date(If(Len(Trim([so_user_Promise_date]))=0,so_ship_date,date#(so_user_Promise_date,'DD-MM-YYYY')),'DD-MM-YYYY') AS so_promise_date
change the first DD-MM-YYYY with the date format that the string field so_user_Promise_date has. Change the second to however you want your dates displayed.
Thank You. That combination worked.