topic Re: Issue with Date Formatting in QlikView

Issue with Date Formatting

Anonymous — Wed, 23 Dec 2015 05:58:15 GMT

Hello Everyone,

I am having DOB from excel and formatting it like this:

date(Date#(DOB,'DD/MMM/YYYY')) as Test,

After running it I am getting Test as a blank field..any suggestion?

Regards,

Balraj Ahlawat

Re: Issue with Date Formatting

tamilarasu — Wed, 23 Dec 2015 06:04:39 GMT

Hi Balraj,

Simply try,

Date(DOB) as Test,

Re: Issue with Date Formatting

Anonymous — Wed, 23 Dec 2015 06:06:21 GMT

again Blank

Re: Issue with Date Formatting

Anonymous — Wed, 23 Dec 2015 06:08:29 GMT

I am having one more field of Date from same excel and into same format, which is working fine

But not on this field

Re: Issue with Date Formatting

amit_saini — Wed, 23 Dec 2015 06:08:35 GMT

Try:

load *, Date(Alt(Date#(DOB, 'DD/MMM/YYYY'), Date#(DOB, 'DD/MMM/YYYY'), Date#(DOB, 'DD/MMM/YYYY')), 'DD-MM-YYYY') as Test;

Thanks,
AS

Re: Issue with Date Formatting

vikasmahajan — Wed, 23 Dec 2015 06:08:55 GMT

check your excel date format whether it is 'DD/MMM/YYYY' becuase date# is interpreting date check with this thread

Re: Date#() function: Worng type of output

HTH

Vikas

Re: Issue with Date Formatting

Anonymous — Wed, 23 Dec 2015 06:11:59 GMT

No luck, again blank.

If I am loading the field without any formatting, it is coming into string format...

And If I am applying any formatting, coming blank

Re: Issue with Date Formatting

vikasmahajan — Wed, 23 Dec 2015 06:13:07 GMT

can you try to change excel format -> Custom -> DD/MM/YYYY and then check

Re: Issue with Date Formatting

tresB — Wed, 23 Dec 2015 06:13:22 GMT

Share your sample excel and let us check how exactly the DOB data is coming from there.

Re: Issue with Date Formatting

vikasmahajan — Wed, 23 Dec 2015 06:14:11 GMT

Recently I also got such issue resolved with changing format date in excel DD/MM/YYYY and appy date#

Re: Issue with Date Formatting

tamilarasu — Wed, 23 Dec 2015 06:16:30 GMT

Could you attach sample file?

Re: Issue with Date Formatting

jagan — Wed, 23 Dec 2015 06:17:40 GMT

Hi,

Try this options

Date(DOB)

If not working load the DOB without any change and check the format in Qlikview how it is read and then use Date(Date#()) to format the date field.

If not working attach sample Excel file, so that it would be easier to identify the issue.

Regards,

Jagan.

Re: Issue with Date Formatting

Wed, 23 Dec 2015 06:18:23 GMT

Hi,

Try this.

date(Date#(DOB,'DDMMYYYY'),'DD/MM/YYYY') as Test

Re: Issue with Date Formatting

Wed, 23 Dec 2015 06:18:52 GMT

remove the slash (/)

Re: Issue with Date Formatting

Wed, 23 Dec 2015 06:21:33 GMT

The other filed which is working fine... What is the format for that field?

Re: Issue with Date Formatting

Anonymous — Wed, 23 Dec 2015 06:21:42 GMT

Thanks everyone, it worked.

Re: Issue with Date Formatting

jagan — Wed, 23 Dec 2015 06:26:44 GMT

Hi Balraj,

But most of the times this excel files are generated automatically from some other 3rd party apps, so changing the format manually is not a good practice. Because everytime the new file comes you have to manually do this.

Instead you read excel file as it is and then check the format of the date field and using Date(Date#()) convert the format.

Hope this helps you.

Regards,

Jagan.

Re: Issue with Date Formatting

Anonymous — Wed, 23 Dec 2015 06:32:28 GMT

Agreed with you Jagan.

After doing all these settings I came to know that with Date#() function I have to use the excel format of date and then I can use num() & Date() combination.

Instead of doing it manually I am doing it at script level like this:

date(num(date#(DOB, 'Excel Format')),'Required Format') as DOB

Re: Issue with Date Formatting

jagan — Wed, 23 Dec 2015 06:37:06 GMT

Hi,

I think you have to give the Correct Answers to mine instead of your's.

Regards,

Jagan.

Re: Issue with Date Formatting

jagan — Wed, 23 Dec 2015 06:38:13 GMT

Hi,

There is no need of Num() while converting Dates, Date#() itself gives you the numeric date. So the script should be

date(date#(DOB, 'Excel Format') ,'Required Format') as DOB

Regards,

Jagan.