topic Re: Date Format in QlikView

Date Format

akpofureenughwu — Thu, 23 Mar 2017 08:37:08 GMT

I have a date field whose format is MM/DD/YYYY.

My prefered format is DD/MM/YYYY

How do I convert it to the prefered format

Regards

_________________________________________

[Inflow]:

LOAD

[Date]as [Inflow_Date],

[ASSET] as [Inflow_Asset],

[CATEGORY] as [Inflow_Category],

[YEAR] as [Inflow_Year],

[AMOUNT_k] as [Inflow_AMOUNT_k],

[AMOUNT_j] as [Inflow_Amount_j]

FROM [lib://WIN-WIN/xxxxx.xlsx]

(ooxml, embedded labels, table is Inflow);

Re: Date Format

ahaahaaha — Thu, 23 Mar 2017 08:45:48 GMT

Hi,

try

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

Regards,

Andrey

Re: Date Format

rahulpawarb — Thu, 23 Mar 2017 08:46:51 GMT

Hello Akpofure,

Please refer below given sample expression:

//Date# function will interpret [Date] field in 'MM/DD/YYYY' format and
//Date function will convert it into 'DD/MM/YYYY' format
Date(Date#([Date], 'MM/DD/YYYY'), 'DD/MM/YYYY') AS [Inflow_Date]

Hope this will be helpful.

Regards!

Rahul

Re: Date Format

sunny_talwar — Thu, 23 Mar 2017 08:59:00 GMT

Re: Date Format

akpofureenughwu — Thu, 23 Mar 2017 09:10:53 GMT

Thanks you Rahul and Andrey... I actually did that.. but I'm getting a blank screen on my froint end.. See attached

Re: Date Format

sunny_talwar — Thu, 23 Mar 2017 09:14:17 GMT

Can you create Date without any manipulation and share a screenshot to show its format? I have a feeling that it might not be MM/DD/YYYY

Re: Date Format

akpofureenughwu — Thu, 23 Mar 2017 09:24:53 GMT

Here is the screenshot of the date without manipulation..

MM/DD/YYYY

Re: Date Format

sunny_talwar — Thu, 23 Mar 2017 09:26:16 GMT

I don't see any screenshot

Re: Date Format

akpofureenughwu — Thu, 23 Mar 2017 09:31:57 GMT

oops

Here it is

Re: Date Format

sunny_talwar — Thu, 23 Mar 2017 09:37:46 GMT

May be add Trim function to make sure that there are not extra spaces

Date(Date#(Trim(FieldDate), 'MM/DD/YYYY'), 'DD/MM/YYYY')

Re: Date Format

tresB — Thu, 23 Mar 2017 09:38:30 GMT

It is already being read as proper date, so only date() should be enough to change the format. Try like:

Date( [Date], 'DD/MM/YYYY') AS [Inflow_Date]

Re: Date Format

sunny_talwar — Thu, 23 Mar 2017 09:39:58 GMT

True, but why would Date# fail still?

Re: Date Format

tresB — Thu, 23 Mar 2017 09:44:00 GMT

Screen shot he is sharing probably from a listbox where the number format could have been set differently. For example, the original field might be having other timestamps like hhmmss..for which date#() might be failing.

Re: Date Format

sunny_talwar — Thu, 23 Mar 2017 09:46:25 GMT

That makes complete sense ... Thanks

Re: Date Format

akpofureenughwu — Thu, 23 Mar 2017 09:49:17 GMT

I tried the above format but it's still not responding

Re: Date Format

sunny_talwar — Thu, 23 Mar 2017 09:52:32 GMT

Try tresesco‌'s suggestion

Re: Date Format

akpofureenughwu — Thu, 23 Mar 2017 09:57:39 GMT

Hello Treseco

Thanks for stopping by...

The sample file I used for this design is attached to this feed.

The date field is in this format: MM/DD/YYYY

Re: Date Format

akpofureenughwu — Thu, 23 Mar 2017 09:58:47 GMT

Please note that the excel file is loaded into the qlik app via SQL