topic Help with Dates in QlikView

Help with Dates

dthornburg — Tue, 23 Apr 2013 12:54:39 GMT

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?

Re: Help with Dates

Gysbert_Wassenaar — Tue, 23 Apr 2013 13:13:12 GMT

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.

Re: Help with Dates

dthornburg — Tue, 23 Apr 2013 13:25:38 GMT

Thank You. That combination worked.