Date format Issue

[bimala0507]

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

[anbu1984]

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.

[bimala0507]

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.

[renjithpl]

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

[PradeepReddy]

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.

[anbu1984]

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

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

[jonathandienst]

Hi

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

[manojkulkarni]

Try something like this

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

It worked for me.

[#9]

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

[Anonymous]

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