topic Re: Date Conversion Problem in QlikView

Date Conversion Problem

Sun, 22 Feb 2015 14:24:44 GMT

Hi All, I am facing a problem with the date conversion.

I have this excel file in which i have a date column in which i intend to have dates in 'DD/MM/YYYY' format.

But when i import this file into qlikview it appears in 'MM/DD/YYYY'.

Converting these dates using the :

date(date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') as date lends me null values in the frontend.

I also tried using date(makedate(subfield(date,'/',3),subfield(date,'/',2),subfield(date,'/',1)),'DD/MM/YYYY')

but still i am getting null values. I am not able to fetch dates correctly to the application. I think its a problem of excel file, but i am not able to identify it correctly and solve it.

Any help would be appreciated.

Thanks in advance.

P.S. : Attaching the excel file which contains date column.

Re: Date Conversion Problem

Anonymous — Sun, 22 Feb 2015 14:39:41 GMT

Just this should suffice :

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

Re: Date Conversion Problem

Anonymous — Sun, 22 Feb 2015 14:42:46 GMT

Hi,

its format issue,not interpretation issue

try:

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

Regards

Neetha

Re: Date Conversion Problem

Sun, 22 Feb 2015 15:24:14 GMT

Hi...But this isn't working as well.

I am still getting dates in 'MM/DD/YYYY' format.

Re: Date Conversion Problem

Sun, 22 Feb 2015 15:24:52 GMT

Hi...But this isn't working as well.

I am still getting dates in 'MM/DD/YYYY' format.

I need the dates in 'DD/MM/YYYY' format

Re: Date Conversion Problem

maxgro — Sun, 22 Feb 2015 15:31:19 GMT

this is what i get loading your excel file

SET DateFormat='MM/DD/YYYY';

LOAD date FROM doubt.xlsx

(ooxml, embedded labels, table is Sheet3);

SET DateFormat='DD/MM/YYYY';

LOAD date FROM doubt.xlsx

(ooxml, embedded labels, table is Sheet3);

Re: Date Conversion Problem

Sun, 22 Feb 2015 17:40:37 GMT

Exactly....

My date is 1st May 2015(01/05/2015),1st june 2015(01/06/2015) and so on....

The '1' is the date part..not the month....

But its showing otherwise and thats the problem.

Re: Date Conversion Problem

Anonymous — Sun, 22 Feb 2015 19:23:56 GMT

Hi Shashi,

I used your excel spreadsheet:

script:

LOAD date
FROM

(ooxml, embedded labels, table is Sheet3);

in UI Date:

for me its working fine

Regards

Neetha

Re: Date Conversion Problem

Mon, 23 Feb 2015 04:34:22 GMT

Hi Neetha,

The output u showed is coming for me too...Th issue i am facing is when i want to display in 'DD/MM/YYYY' format.

The 01 is the date. 05,06,07 etc.. are the months..

When i apply date(date,'DD/MM/YYYY') to it i still get in 'MM/DD/YYYY' format.

Its treating the 01 as month instead of date.

Re: Date Conversion Problem

jonathandienst — Mon, 23 Feb 2015 05:23:12 GMT

Shashi Nadiger wrote:

The 01 is the date. 05,06,07 etc.. are the months..

Not true. In the Excel file you attached, 01 is the month and 05, 06, 07 are the days, so you are getting exactly what is in your source file.

HTH

Jonathan

Re: Date Conversion Problem

Mon, 23 Feb 2015 05:30:40 GMT

Hi,

you can convert date into number format.

when you show in chart date(date,'DD/MM/YYYY') use like this or

go chart properties->number->date and change format.

Regards

Vimlesh

Re: Date Conversion Problem

prma7799 — Mon, 23 Feb 2015 05:31:13 GMT

Hi Shashi,

Can you post some small application for the same.

Thanks

Re: Date Conversion Problem

Mon, 23 Feb 2015 05:41:27 GMT

Hi,

Your Excel Date formate

So that 01 is Month and 05,06,07 are days.

Re: Date Conversion Problem

PrashantSangle — Mon, 23 Feb 2015 05:44:56 GMT

Hi,

Jonathan is wright about date format.

Your date in xls is in DD/MM/YYYY format.

Therefore you are getting this output.

Check your xls.

Regards

Re: Date Conversion Problem

fashid — Mon, 23 Feb 2015 05:59:05 GMT

HI,

BUT CANT WE CHANGE THE DATE FORMAT USING THE DATE# FUNCTION IN QLIKVIEW

REGARDS,

NADEEM

Re: Date Conversion Problem

Mon, 23 Feb 2015 06:05:38 GMT

Hi,

See i need the 01 to be interpreted as date. In excel file i had intended the date to be in DD/MM/YYYY format but instead , the excel file is reading it as MM/DD/YYYY format.

I checked in the excel and i found that 01 is being interpreted as month. What should i do or make changes in the excel.

Re: Date Conversion Problem

Mon, 23 Feb 2015 06:06:45 GMT

Ya i saw that thank you. But i need the 01 to be interpreted as date. What should i do for that??

Re: Date Conversion Problem

Mon, 23 Feb 2015 06:19:12 GMT

Hi all,

Thanks for your help, Really appreciate it.

I managed to get things done by making use of makedate().

I have attached what i needed.

Re: Date Conversion Problem

Mon, 23 Feb 2015 06:22:20 GMT

Hi,

You can't change date in excel by using any format.

If you want to change the date then you should add .

Use this expression in script

makedate(year(date),day(date),month(date) ) as DATE111,

Regards

Vimlesh

Re: Date Conversion Problem

fashid — Mon, 23 Feb 2015 06:24:09 GMT

Hi,

Could you please attach the formula or the script that you used

would be helpful for learners like me .

Regards,

Nadeem