Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dthornburg
Contributor II
Contributor II

Help with Dates

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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
dthornburg
Contributor II
Contributor II
Author

Thank You. That combination worked.