Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
afuchten
Valued Contributor

DateTime to Date

Hi all,

Think this shouldn't be a difficult one ... but just can't find the correct answer ...

I have a load scipt containing a DateTime value

LOAD .... modificationdatetime, ... FROM ...


I also have a calendar to which I want the modificationdatetime point to ... Sounds easy?


LET vMinDate = NUM(DATE('1-1-2004'));
LET vMaxDate = NUM(TODAY());

TempCalendar:
LOAD
$(vMinDate)+ITERNO() AS NUM,
DATE($(vMinDate)+ITERNO()-1) AS TempDate

AUTOGENERATE (1) WHILE
$(vMinDate)+ITERNO()-1 <= $(vMaxDate);

ModificationDateCalendar:
LOAD
TempDate AS modificationdatetime,
YEAR(TempDate) AS Year,
APPLYMAP('Quarter', NUM(MONTH(TempDate))) AS Quarter,
MONTH(TempDate) AS Month,
WEEK(TempDate) AS Week,
WEEKDAY(TempDate) AS WeekDay,
DAY(TempDate) AS Day,
DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear

RESIDENT
TempCalendar
ORDER BY
TempDate;
DROP TABLE TempCalendar;


Does not seem to be wrong ...

But now a way to find to get the correct date connection.

Did try the following

date(modificationdatetime)
date(modificationdatetime, 'DD-MM-YYYY')
date(num(modifcationdatetime))

In all these situations the date is displayed correctly, but the connection with the calendar seems to be wrong, since I don't see anything when selection the Year or something.

Any suggestions??

Tags (2)
1 Solution

Accepted Solutions
atukums
Contributor II

DateTime to Date

Date function returns only format, but does not change the value.

You need to use MakeDate function like this:

MakeDate(Year(modificationdatetime),Month(modificationdatetime),Day(modificationdatetime))

Rds,
AT

5 Replies
atukums
Contributor II

DateTime to Date

Date function returns only format, but does not change the value.

You need to use MakeDate function like this:

MakeDate(Year(modificationdatetime),Month(modificationdatetime),Day(modificationdatetime))

Rds,
AT

afuchten
Valued Contributor

DateTime to Date

thx ... it was really easy ... just couldn't figure it out Big Smile

Not applicable

DateTime to Date

Hi,

If you have a Timestamp you need to get the date isolated:

FLOOR(modificationdatetime) AS modificationdatetime


Hope this helps,

Matthias

MVP
MVP

DateTime to Date

I tend to use floor() myself, but if I weren't already in that habit, the daystart() function would probably be a slightly better choice. Same exact numeric result, but floor() is a mathematical function that takes advantage of QlikView's internal formats, while daystart() is a date/time function that doesn't depend on the internal format. Very minor point overall, though, as I'm pretty confident that QlikView will never change the way they represent dates and times.

Not applicable

Re: DateTime to Date

Hi Matthias, Your answer is so much helpful. Thank You.