topic Re: Problem with dates in QlikView

Problem with dates

Fri, 18 Apr 2014 12:30:36 GMT

Hi,

I'm using the followin script:

Data:
select
(select top 1 name from labelfile where labeldefinition='ReasonLate' and abbr=stp_reasonlate) as reasonlate,
stp_schdtlatest,
stp_arrivaldate,
lgh_Carrier,
orderheader.ord_hdrnumber as TMWO

from stops
left join orderheader on orderheader.ord_hdrnumber=stops.ord_hdrnumber
left join legheader on stops.lgh_number=legheader.lgh_number
where (stp_arrivaldate>getdate()-31) and
((stp_reasonlate<>'UNK')or(stp_arrivaldate>stp_schdtlatest));

Data2:
Load
date(stp_schdtlatest) as Date, *

Resident Data;

DROP table Data;

Now in my table, I want the dimension "Date" on the vertical axis. However, I get something like this:

How do I combine all date for one date in my table? (so all the data combined, and in the dimension only once 18-03, 19-03, etc.)

Thanks!

Niek

Re: Problem with dates

Anonymous — Fri, 18 Apr 2014 12:34:11 GMT

Use

date(floor(stp_schdtlatest)) as Date

Your stp_schdtlatest field is obviously timestamp, not a date. Function floor() removes the fraction part (time)

Re: Problem with dates

rustyfishbones — Fri, 18 Apr 2014 12:34:49 GMT

It probably means your Date is not formatted correctly

Re: Problem with dates

alexandros17 — Fri, 18 Apr 2014 12:35:20 GMT

It seems you have date - time field, if it is so, when you load try to cast the date as DD/MM/YYYY

Re: Problem with dates

Fri, 18 Apr 2014 12:36:25 GMT

Thanks!

Re: Problem with dates

rustyfishbones — Fri, 18 Apr 2014 12:41:18 GMT

YOU CAN TRY