topic Re: Date format Issue in QlikView

Date format Issue

bimala0507 — Fri, 13 Feb 2015 03:01:04 GMT

Dear Experts:

Can anyone throw some light on the date issue, I am encountering. To my understanding QlikView stores Date field as Number. I have a date field for which it fails to do so, so in the script, I tried to convert it into Date format by using makedate() function, but no result, in fact the expression return NULL inside the script, however outside it works perfectly fine. AnyIdea how do I handle this date, so that it will be stored in Date format.

Kind Regards,

Bimala

Re: Date format Issue

anbu1984 — Fri, 13 Feb 2015 03:09:31 GMT

Yes Qlikview stores Date field as Number

What is the source of data? Inline data or Table?

EDIT: Can you post what you tried using MakeDate() and sample input.

Re: Date format Issue

bimala0507 — Fri, 13 Feb 2015 04:45:35 GMT

It is from the Sybase database. The formula used is

makedate(Right(inv_date,4),mid(inv_date,(Index(inv_date,'/',1)+1),(Index(inv_date,'/',2))-(Index(inv_date,'/',1)+1)),left(inv_date,(Index(inv_date,'/',1)-1)))

Where inv_date is the date, I am talking about. It has value such as

7/72014

31/7/2014

1/12/2014

it is in DD/MM/YYYY format.

The expression works fine as an expression.

Re: Date format Issue

renjithpl — Fri, 13 Feb 2015 05:20:03 GMT

May be like this...

Date(MakeDate(SubField(Inv_Date, '/', 3),SubField(Inv_Date, '/', 2), SubField(Inv_Date, '/', 1)) , 'DD/MM/YYYY') as Invoice_Date

Cheers

Re: Date format Issue

PradeepReddy — Fri, 13 Feb 2015 05:28:44 GMT

try some thing like this... using makedate() &subfield() functions.

=makedate(subfield(inv_date,'/',3),subfield(inv_date,'/',2),subfield(inv_date,'/',1))

There might be NULL values or junk data in the Database for this field.

Re: Date format Issue

anbu1984 — Fri, 13 Feb 2015 05:29:22 GMT

You can use Date#() like below instead of MakeDate()

Date#(inv_date, 'DD/MM/YYYY')

Re: Date format Issue

jonathandienst — Fri, 13 Feb 2015 05:29:49 GMT

Let QV do the work for you:

Date ( Alt(Date#(inv_date, 'D/M/YYYY'), Date#(inv_date, 'DD/MM/YYYY')) )

Alt wil return the first valid date in the list of date interpretation (Date#) calls.

HTH

Jonathan

Re: Date format Issue

manojkulkarni — Fri, 13 Feb 2015 05:39:57 GMT

Try something like this

Date('7/7/2014','DD-MM-YYYY')

It worked for me.

Re: Date format Issue

Sat, 14 Feb 2015 07:48:34 GMT

Hello Bimala,

I had the same problem and found a very simple solution.

I put

Date (datum, 'DD.MM.YY') as Datefield,

in the load script. Datum is the field which contains the original date from my database, datefield I put into the dimension to have it as x-axes.

Kind regards

Peter

Re: Date format Issue

Anonymous — Sat, 14 Feb 2015 18:44:12 GMT

Jonathan,

Alt() wouldn't hurt of course, but from the OP looks it is not necessary. Just

Date(Date#(inv_date, 'D/M/YYYY'))

Regards,

Michael