Trouble with format dates

[pgalvezt]

Hi, I have 2 dates ActualDate and LastDate. With both dates i want to get an incremental reload with Qvds Files. Until now everything is fine but, in my access data appear ActualDate with 2 formats example: 21/03/2011 -> March 21(this is the correct format) and the other one 04-06-2011 -> June 04, Ithink Should be April 06(I think we are not at this time ). How can I get that QlikView read 04/06/2011-> April 06?

Thanks,

[nagaiank]

Try using Date# function with the format code MM/DD/YYYY.

e.g. Date#(datestring, 'MM/DD/YYYY')

[pgalvezt]

Thank you for your replying. I have this:

LOAD

DATA,

date(DATA,'DD/MM/YYYY') as DataActual,

DATA1,

date(DATA1,'DD/MM/YYYY') as DataLast

FROM Excel File;

This shows me no values for DATA because here the format changed.

For DATA1 Shows me the right format.

Avy idea?

[erichshiino]

If you get this 04/06/2011 how do you know which date is it?

What about 02/03/2011? Is it 02-mar-2011 or 03-feb-2011?

You could write something like this:

if( isnum( date#(DATA,'DD/MM/YYYY') ),

date#(DATA,'DD/MM/YYYY'),

date#(DATA,'MM/DD/YYYY') )

  as DataActual

This code will try to apply the first format, if it's not a valid date, applies the second format.

You will still need a rule to really understand which is the right format

Regards,

Erich

[pgalvezt]

Then reloading with the update script I had this: For data I know that / format its ok D/MM/YYYY but - Format its MM/DD/YYYY.

For data 1 everything its ok.

Any Idea?

[erichshiino]

HI,

Lets include another condition. If it finds a - it will use another format

IF( SubStringCount(DATA, '-')> 0, DATE#(DATA, 'MM-DD-YYYY') ,

if( isnum( date#(DATA,'DD/MM/YYYY') ),

date#(DATA,'DD/MM/YYYY'),

date#(DATA,'MM/DD/YYYY') )

)

  as DataActual

edit: just removed a line with an error

[pgalvezt]

Hi, I try this:

IF( SubStringCount(DATA, '-')> 0,

     DATE#(DATA,'MM-DD-YYYY'),

date#(DATA,'DD/MM/YYYY')) as DataActual,

But still can´t find the solution.

I´ve Attached the datasource where you can see the different format in column DATA.

[#8]

Try this:

IF( SubStringCount(DATA, '-')> 0,

     DATE(DATE#(DATA,'MM-DD-YYYY'),'MMMM DD'),

     DATE(DATE#(DATA,'DD/MM/YYYY'),'MMMM DD'))      as DataActual,

Regards,

Janusz

[pgalvezt]

Hi Janusz, Thanks for you answer but I still have the same Issue. The results that shows before still appearing of the same form.

[#10 · Accepted solution]

It can't be. Please look into attached example.

Load script looks like this:

Test:

LOAD * INLINE [

    DATA

    05-20-2011

    20/06/2011

];

Test2:

load

          IF( SubStringCount(DATA, '-')> 0,

               DATE(DATE#(DATA,'MM-DD-YYYY'),'MMMM DD'),

               DATE(DATE#(DATA,'DD/MM/YYYY'),'MMMM DD'))      as DataActual

resident Test;