topic Re: Trouble with format dates in QlikView

Trouble with format dates

pgalvezt — Mon, 30 May 2011 17:19:00 GMT

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,

Trouble with format dates

nagaiank — Mon, 30 May 2011 17:30:20 GMT

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

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

Trouble with format dates

pgalvezt — Mon, 30 May 2011 19:11:04 GMT

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?

Trouble with format dates

erichshiino — Mon, 30 May 2011 19:39:00 GMT

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

Trouble with format dates

pgalvezt — Tue, 31 May 2011 20:16:19 GMT

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?

Re: Trouble with format dates

erichshiino — Tue, 31 May 2011 20:27:35 GMT

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

Re: Trouble with format dates

pgalvezt — Wed, 01 Jun 2011 14:26:16 GMT

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.

Re: Trouble with format dates

Wed, 01 Jun 2011 14:39:58 GMT

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

Re: Trouble with format dates

pgalvezt — Wed, 01 Jun 2011 14:49:51 GMT

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

Re: Trouble with format dates

Wed, 01 Jun 2011 14:53:49 GMT

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;

Re: Trouble with format dates

Miguel_Angel_Baeyens — Wed, 01 Jun 2011 15:14:40 GMT

Hi,

Interpretation and representation in QlikView for money, decimal and thousand separators, dates, etc, is subject to your computer's regional settings. According to that, if you go to your script editor, main tab, the first lines are the environment variables that tell QlikView how to interpret and represent, in this file, those formats.

If you cannot change that format in the source, you will need to change these variables to fit the source, kind of

SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff]';

Obviously, this settings are document wide, and they will affect to any other date you have.

Hope that helps.

Trouble with format dates

pgalvezt — Wed, 01 Jun 2011 15:17:03 GMT

You´re right something in my code was wrong but with your example everithing its clear Thank you Both of you guys.