topic Re: makedate function generates an integer as opposed to the actual date in QlikView

makedate function generates an integer as opposed to the actual date

happyjoshua777 — Wed, 25 Nov 2020 16:16:04 GMT

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/DateAndTimeFunctions/makedate.htm

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

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

in order to end up with the expected date :

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

Re: makedate function generates an integer as opposed to the actual date

adityaakshaya — Wed, 04 Jul 2018 05:32:23 GMT

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

Re: makedate function generates an integer as opposed to the actual date

happyjoshua777 — Wed, 04 Jul 2018 06:37:04 GMT

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

Re: makedate function generates an integer as opposed to the actual date

BalaBhaskar_Qlik — Wed, 04 Jul 2018 10:26:15 GMT

may be:

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

Re: makedate function generates an integer as opposed to the actual date

Anil_Babu_Samineni — Wed, 04 Jul 2018 11:12:17 GMT

Can you post real values to test?

Re: makedate function generates an integer as opposed to the actual date

happyjoshua777 — Wed, 04 Jul 2018 11:31:23 GMT

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

Re: makedate function generates an integer as opposed to the actual date

thevingo — Wed, 04 Jul 2018 11:55:19 GMT

Can you check default date format

SET DateFormat='M/D/YYYY';

Re: makedate function generates an integer as opposed to the actual date

Miguel_Angel_Baeyens — Wed, 04 Jul 2018 12:07:50 GMT

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?)