Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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:MMSmiley FrustratedS, 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
MVP
MVP

Re: Time & date interpretation

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

   Date(Floor(Date#(dateField, 'YYYY-MM-DD HH:MMSmiley FrustratedS'))) 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
MVP
MVP

Re: Time & date interpretation

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

Re: Time & date interpretation

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

DayName()

regards

Marco

hirishv7
Honored Contributor

Re: Time & date interpretation

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

Re: Time & date interpretation

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

Community Browser