topic Re: Time & date interpretation in QlikView

Time & date interpretation

Tue, 05 Apr 2016 10:09:07 GMT

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

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

Re: Time & date interpretation

jonathandienst — Tue, 05 Apr 2016 15:57:12 GMT

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.

Re: Time & date interpretation

jonathandienst — Tue, 05 Apr 2016 15:59:10 GMT

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.

Re: Time & date interpretation

MarcoWedel — Wed, 06 Apr 2016 23:21:51 GMT

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

DayName()

regards

Marco

Re: Time & date interpretation

HirisH_V7 — Thu, 07 Apr 2016 05:53:13 GMT

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:

HTH,

PFA,

Hirish

Re: Time & date interpretation

Kushal_Chawda — Thu, 07 Apr 2016 06:01:19 GMT

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