Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Time & date interpretation

Hi,

Newbie question perhaps....

I'm using QV to analyze a large logfile that is generated once per week. The logfile contains a combined date and time field which is loaded in to my application and looks like this by default:

YYYY-MM-DD HH:MM:SS, example: 2016-04-02 00:04:29

The logfile contains several hundered lines that have the same date, but different time stamps (seconds between them)

My application doesn't depend on the timestamps, I only need the date. By modifying the load script I'm able to bypass the timestamp, however then I create a listbox containing the date field my application lists every single line containing a date, se my example below:

2016-04-02

2016-04-02

2016-04-02

My assumption is that the listbox shows me all of the dates equivalent to the number of occurrences, not by "grouping" the dates.

I would like the listbox to only contain one date at a time, example below:

2016-04-02

2016-04-03

2016-04-04

Regards,


Nicklas

5 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

When yu load the date, you need to truncate the times:

   Date(Floor(Date#(dateField, 'YYYY-MM-DD HH:MM:SS'))) as Date,

Replace the field name and the alias per your requirements.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

Note that Date() on its own does not change the underlying value. It formats the text form of the date/time value, but does not remove the time, so you land up with a whole lot of different values that look the same.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MarcoWedel

to format a timestamp as date and to remove the time part you can also use

DayName()

regards

Marco

HirisH_V7
Master
Master

Hi,

May be like this,

Data:

LOAD *,

Date(Timestamp(Field,'YYYY-MM-DD h:mm:ss'),'YYYY-MM-DD') as DateField

INLINE [

    Field

    2016-04-02 00:04:29

    2016-04-03 00:04:29

    2016-04-04 00:04:29

];

Front end:

Timestamp-212135.PNG

HTH,

PFA,

Hirish

HirisH
Kushal_Chawda

another way is to generate the calendar and link the Calender Date to actual date in data.

Dates:

LOAD distinct max(Date) as MaxDate,

min(Date) as MinDate

Resident Yourtable;

let vMax = peek('MaxDate',0,'Dates');

let vMin = peek('MinDate',0,'Dates');


Calendar:

LOAD *,

             Year(Date) as Year,

             month(Date) as Month;

LOAD Date($(vMin) + iterno()-1) as Date

autogenerate 1

while $(vMin) + iterno()-1 <= $(vMax);