topic Re: How extract Data without ime from db in QlikView

How extract Data without ime from db

pascos88 — Fri, 23 Mar 2018 13:12:36 GMT

Hello,

I have some problem with the extraction of the data.

In my db the data type format is DD.MM.YYYY hh:mm:ss.

Of course if I put a filter on the date I have different rows for instance:

PERSON DAY

A 05.11.2017 13:55:20

B 05.11.2017 12:50:44

C 05.11.2017 09:09:23

I try with load data as Data (DAY, 'DD.MM.YYYY') or using the left(DAY,11) but in both cases when I use the Filter pane, I can see three time the some date.

Why I cannot see just once even if the data looks the same?

Thanks for any suggestion,

Best

Re: How extract Data without ime from db

YoussefBelloum — Fri, 23 Mar 2018 13:16:50 GMT

Hi,

it is happening for me sometimes..

are you formatting your DAY field on the script or on the front-end ?

Re: How extract Data without ime from db

pascos88 — Fri, 23 Mar 2018 13:18:38 GMT

Hi, I formatting the data in the data load..

Re: How extract Data without ime from db

YoussefBelloum — Fri, 23 Mar 2018 13:20:27 GMT

Hi stalwar1‌,

do you have an idea here ?

when we transform timestamp field to a date field, and with data like we have above, sometimes, I have three lines on a listbox and sometimes I have only one (which is the correct one).

it depends on the way we format a timestamp ?

Re: How extract Data without ime from db

sunny_talwar — Fri, 23 Mar 2018 13:28:16 GMT

Date() is just a formatting function... by using Date() you are just making it look like there is no time component, but the underlying value is still timestamp. In order to get rid of time... you can either use Floor() or you can use DayName function

DayName(Day) as Date

Date(Floor(Day), 'DD.MM.YYYY') as Date

Re: How extract Data without ime from db

YoussefBelloum — Fri, 23 Mar 2018 13:35:38 GMT

very clear !

and Date#() is not recommended either ?

Re: How extract Data without ime from db

sunny_talwar — Fri, 23 Mar 2018 14:15:43 GMT

Date#() is interpretation... it is useful if Qlik doesn't understand your date as date....

Re: How extract Data without ime from db

YoussefBelloum — Fri, 23 Mar 2018 14:23:49 GMT

I know this. I was asking if using Date#() on a timestamp will really remove Time from it..

I'll test it out

Re: How extract Data without ime from db

sunny_talwar — Fri, 23 Mar 2018 14:29:35 GMT

Again, Date#() is an interpretation function... can't really add/remove anything... all it does is to help Qlik Interpret Date.... It can be combined with Left()/Right() function and then it will work...but alone it cannot.

For example:

Date#(Left('05.11.2017 13:55:20', 10), 'DD.MM.YYYY') as Date

Here there won't be any time component...

Re: How extract Data without ime from db

YoussefBelloum — Fri, 23 Mar 2018 14:53:46 GMT

Thank you Buddy