Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
dthornburg
Contributor
Contributor

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

View solution in original post

dthornburg
Contributor
Contributor
Author

Thank You. That combination worked.