Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

makedate function generates an integer as opposed to the actual date

Hello,

I used makedate(2099,12,31) expecting it to generate the actual date, at least according to:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFun...

Instead I kept getting '73050' which is an integer equiv. of 31/12/2099:integer.png

I had to resort to date(makedate(2099,12,31),'DD/MM/YYYY')

in order to end up with the expected date :

date.png

This is the command in QlikView:

if(len(date(EndDate,'DD/MM/YYYY')) < 1, date(makedate(2099,12,31),'DD/MM/YYYY'), date(EndDate,'DD/MM/YYYY')) as EndDate

Is makedate function behaving as expected? If, yes, does that mean that the offical article in help.qlik.com is outdated?

thanks

1 Solution

Accepted Solutions
Highlighted

Yes, that is exactly what is happening.

QlikView uses by default, when creating a new document, the locale settings of the user session creating it. In my case, I have set Windows to English (US), but some of my colleagues have Swiss settings, so their date format is "DD.MM.YYYY"

The result of MakeDate() is correct in any case. It is worth mentioning that QlikView does not have dates as different data types: any value is susceptible to have a numeric representation and a literal representation. In the case of Date() related functions, day 1 (numeric) is 31/12/1899 (literal, using mask DD/MM/YYYY) and today is day 43285.

If the data is coming with a different format than the one in your environment variable DateFormat you will need to use, for the fields to be displayed in the front-end, functions like Date() with masks, otherwise the literal representation will not be possible and QlikView will revert to the numeric value.

You can also change that DateFormat variable, but then make sure that all the data you are loading is using the format you specify.

EDIT: the numeric values for dates, by the way, are underused by most of the developers I know. There is nothing wrong in using 43285 in functions to compare dates, get intervals, extract months, calculate age, etc. I only use Date() when I want to display a value, for which I always use the mask to avoid that, in case other developers or users open my application, they see a different value (4.7.2018 is April or July?)

View solution in original post

7 Replies
Highlighted
Creator III
Creator III

Hi Happy,

makedate function always return date . In your case, makedate is not behaving as expected.

Ca nyou share the application with some data, so that, I can see and let you know why are getting such results.

Regards,

Akshaya

Highlighted
Creator
Creator

Loading from Excel file column 'EndDate'

if employee if still working in the company then the respective field is empty

Otherwise there is there is a date of resignation, such as '

05-01-97

In QlikView I created Employee table with

...

    if(len(date(EndDate,'DD/MM/YYYY')) < 1, makedate(2099,12,31), date(EndDate,'DD/MM/YYYY')) as EndDate,

...

to generate EndDate column.

The end result is as per the first screen shot which is '73050' is getting generated by makedate()

Could this be due to:

    SET DateFormat='DD-MM-YY';

in the declaration section of my script?

I think this is related to the issue at hand:

In another qvw file for

    addyears(date(MyDate,'DD/MM/YYYY'),20)

The output is: 42856

which means I have to use date() for the second time as follows:

    date(addyears(date(MyDate,'DD/MM/YYYY'),20))

just to have a proper date value as my output 01/05/2016

I also have   SET DateFormat='DD-MM-YY';

declared in the beginning

     Note: MyDate is another column I was traversing in this second qvw

thanks

Highlighted

may be:

if(len(date#(EndDate,'DD/MM/YYYY')) > 0, makedate(2099,12,31), date(EndDate,'DD/MM/YYYY')) as EndDate.

Highlighted

Can you post real values to test?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

EndDate
05-01-97
31-12-97

Tried pasting the actual column from Excel. All other values in Excel rows are empty.


MyDate Excel column:

 

MyDate
08-12-48
19-02-52
30-08-63
19-09-37
04-03-55
02-07-63
29-05-60
09-01-58
27-01-66
30-08-63
29-05-60
Highlighted
Creator
Creator

Hi

Can you check default date format

SET DateFormat='M/D/YYYY';

Highlighted

Yes, that is exactly what is happening.

QlikView uses by default, when creating a new document, the locale settings of the user session creating it. In my case, I have set Windows to English (US), but some of my colleagues have Swiss settings, so their date format is "DD.MM.YYYY"

The result of MakeDate() is correct in any case. It is worth mentioning that QlikView does not have dates as different data types: any value is susceptible to have a numeric representation and a literal representation. In the case of Date() related functions, day 1 (numeric) is 31/12/1899 (literal, using mask DD/MM/YYYY) and today is day 43285.

If the data is coming with a different format than the one in your environment variable DateFormat you will need to use, for the fields to be displayed in the front-end, functions like Date() with masks, otherwise the literal representation will not be possible and QlikView will revert to the numeric value.

You can also change that DateFormat variable, but then make sure that all the data you are loading is using the format you specify.

EDIT: the numeric values for dates, by the way, are underused by most of the developers I know. There is nothing wrong in using 43285 in functions to compare dates, get intervals, extract months, calculate age, etc. I only use Date() when I want to display a value, for which I always use the mask to avoid that, in case other developers or users open my application, they see a different value (4.7.2018 is April or July?)

View solution in original post