Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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

1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

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

View solution in original post

5 Replies
Highlighted
Creator II
Creator II

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

View solution in original post

Highlighted
Specialist
Specialist

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

Highlighted
Not applicable

Hi,

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

FLOOR(modificationdatetime) AS modificationdatetime


Hope this helps,

Matthias

Highlighted
MVP
MVP

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.

Highlighted
Not applicable

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