topic Re: Re: How to get date properly in QlikView

How to get date properly

Mon, 20 Jan 2014 10:29:54 GMT

Hi Everyone!

I am getting data onto Qlikview from excel. In it is a column for date, but its formatted in a way that both date and time are present in the cell, for example:

2014-01-19 23:59:33.0

Now, from it I only need date, not the time. I tried using the date function, but when i use it as a list box, the same date turns up multiple times( as many times as its entries in the excel)

I need the date as unique values in a list, which function can i use?

Thanks in advance for all the queries.

Re: How to get date properly

Mon, 20 Jan 2014 10:37:32 GMT

try this

date(date#(datefield,'YYYY-MM-DD hh:mm:ss'),'DD-MM-YYYYY')

Re: How to get date properly

datanibbler — Mon, 20 Jan 2014 10:39:28 GMT

Hi,

so you want only the date in QlikView, but in your base_data you have multiple entries per date?

That cannot be done easily - well, you could cut off the time_part using a STRING function, but that would cause you to lose data, wouldn't it?

=> If you don't need multiple records per day, but only one, try using FIRST() or SUBSTRING().

Re: How to get date properly

Mon, 20 Jan 2014 10:43:05 GMT

Trying now. Will keep you posted.

Re: How to get date properly

Mon, 20 Jan 2014 10:44:27 GMT

Hi Datanibbler,

There is only loss of the time part of the data, there are not multiple dates in each cell, just a date and a time.

Re: How to get date properly

Mon, 20 Jan 2014 10:49:10 GMT

Hi,

You can use these;

subfield(F1,' ',1) as date1,
or
date#(left(F1,10),'YYYY-MM-DD') as date

HTH

-Shruti

Re: How to get date properly

sushil353 — Mon, 20 Jan 2014 10:51:52 GMT

Hi..

first of all set the environment variable to the desired format of date

SET DateFormat='MM/DD/YYYY';

after that you can try this expression

date(date#(datefield,'YYYY-MM-DD hh:mm:ss'),'MM/DD/YYYY')

HTH

Sushil

Re: How to get date properly

MK_QSL — Mon, 20 Jan 2014 10:52:36 GMT

Add Below Lines to your script, it will completely remove the time field....

Date(Floor(YourDateField,'YYYY-MM-DD')

Re: Re: How to get date properly

jagannalla — Mon, 20 Jan 2014 10:55:01 GMT

Hi,

Try this,

Date(Floor(DateField),'MM/DD/YYY'Y)

Hope it helps you!!!

Thanks,

Jagan

Re: How to get date properly

Mon, 20 Jan 2014 11:18:26 GMT

Hi Sushil,

This returned a list box which was empty

Re: How to get date properly

arulsettu — Mon, 20 Jan 2014 11:26:09 GMT

Hi try this

Load *, Floor(DateField) as DateField

from tablename;

Re: How to get date properly

Mon, 20 Jan 2014 11:31:39 GMT

Hi Manish,

Unfortunately this also returned an empty list box!

Please HELP!!!!!!!

Re: How to get date properly

Mon, 20 Jan 2014 11:34:12 GMT

Hi, I already tried the floor function, it did not work! This seems so simple, why is it not working!?

Re: How to get date properly

MK_QSL — Mon, 20 Jan 2014 11:34:50 GMT

can you post your application?

If you can't, please post only your date field.

Re: How to get date properly

Mon, 20 Jan 2014 11:36:14 GMT

hey sushant,

Try doing it with just Date() function for example

Date(datefield,'DD-MM-YYYY') as date1

it should work fine

regards

harshal

Re: Re: How to get date properly

Mon, 20 Jan 2014 11:55:50 GMT

Hi Manish,

PFA the excel. Just cant seem to get the data in the right format.

Re: Re: How to get date properly

Mon, 20 Jan 2014 11:57:55 GMT

Hi Harshal

Unfortunately, it did not work!

Re: Re: Re: How to get date properly

tresB — Mon, 20 Jan 2014 12:03:41 GMT

I have used your sample file and it worked. PFA

Re: How to get date properly

jonathandienst — Mon, 20 Jan 2014 12:04:23 GMT

Do not use date(date#()) only - this reformats the date, but does NOT remove the time portion. You must use floor() or a subfield to remove the date:

This is what I suggest you need:

date(floor(Date#(datefield, 'YYYY-MM-DD hh:mm:ss.ff')))

date(floor(Date#('2014-01-19 23:59:33.0', 'YYYY-MM-DD hh:mm:ss.ff')))

HTH

Jonathan

Re: Re: How to get date properly

Mon, 20 Jan 2014 12:08:03 GMT

Hi Tresesco,

Thanks, but i have a desktop version, cant open the file, can you tell me the specific function that you used?

By saying that it worked, you mean that you saw just one date in the list box, as the sample data is just for 1 date?

Thanks for all your help.