topic Convert decimal number to date in QlikView

Convert decimal number to date

Thu, 25 Nov 2010 14:37:52 GMT

First of all I am new (since today) and am saying hello to you all!

I have an application that sees the decimal number decimal number 1 as 31 semptember 1971. After that it is incrementing daily with ++. So today is 14209. How do I convert this to normal date notation ( ex. 25-11-2010)

Thank you in advance!

Convert decimal number to date

Thu, 25 Nov 2010 14:51:27 GMT

Hello,

if I got you right, your source system counts the days starting in Sep1971. QV does it in a similar way. Today in QV is Number 40507. You can proof this while creating a text object with

=num(today())

. Without the num()-function today() returns the date in your date-format.

So with this in mind it should be easy for you to add the necessary offset while loading to get the according dates. Don't forget to tell QV to convert the incoming numbers to be a date similar to this

Load ... date(sourcedate + offset) as MyFirstQVDate;

Regards, Roland

Convert decimal number to date

Thu, 25 Nov 2010 15:08:53 GMT

Thank you for replying this quick. But I don't quite understand.

I have the next in the editor:

LOAD

"sched_offb_dep_date"

as StartDate,

"sched_offb_dep_time"

as StartTime,

"sched_onbl_arr_date"

as EndDate,

"sched_onbl_arr_time"

as EndTime;

SQL

SELECT sched_offb_dep_date, sched_offb_dep_time, sched_onbl_arr_date, sched_onbl_arr_time

FROM

prod.dbo."ac_util_netl_two_hist";

where sched_onbl_arr_time contains the date issue. What do I add so it is not displayed in 14209 but in 25-11-2010.

Convert decimal number to date

Thu, 25 Nov 2010 15:53:37 GMT

Hi again,

try this:


date("sched_offb_dep_date" + 26298) as StartDate,
"sched_offb_dep_time" as StartTime,
date("sched_onbl_arr_date" +26298) as EndDate,
"sched_onbl_arr_time" as EndTime;
. . . . .

Hint: The offset is 26298 --> (QV today - sourcesystem today) = (40507 -14209) . The date() converts numbers into dates.

Convert decimal number to date

Thu, 25 Nov 2010 16:12:59 GMT

Thank you for your explanation, It was very clear now.

I got it to work!