topic Re: Date Format issue in QlikView

Date Format issue

Wed, 19 Aug 2015 07:53:06 GMT

My excel sheet date field have different formates of dates are available..so i need to make a proper format.How can achieve this.

Re: Date Format issue

sasiparupudi1 — Wed, 19 Aug 2015 07:55:25 GMT

post your data file

Re: Date Format issue

qlikviewwizard — Wed, 19 Aug 2015 08:02:29 GMT

Hi Karthik,

Please attach the excel file or your sample application. Thank you.

Re: Date Format issue

Anonymous — Wed, 19 Aug 2015 08:05:59 GMT

as first tip, check "Date#" in Qlikview help

this function may help you

Re: Date Format issue

Wed, 19 Aug 2015 08:21:02 GMT

Date	sales
1/2/2009	400	dd-mm-yyyy
2/13/2009	5000	mm-dd-yyyy
2009/15/2	3000	yyyy-dd-mm
3/17/2009	1	mm-dd-yyyy
Required format "DD-MM-YYYY"

Re: Date Format issue

sunny_talwar — Wed, 19 Aug 2015 08:30:24 GMT

Try this may be:

Date(Alt(Date#(Date, 'DD-MM-YYYY'), Date#(Date, 'MM-DD-YYYY'), Date#(Date, 'YYYY-DD-MM')), 'DD-MM-YYYY')

Re: Date Format issue

sunny_talwar — Wed, 19 Aug 2015 08:31:23 GMT

Only problem would be when you have a date like 01/02/2015. Would you want QlikView to interpret this like Jan 2nd 2015 or Feb 1st 2015?

Re: Date Format issue

vishsaggi — Wed, 19 Aug 2015 08:32:06 GMT

Hi Karthik,

Try this:

TableName:

LOAD Date(Date#(Date, 'DD-MM-YYYY'), 'DD-MM-YYYY') As Date,

Sales

FROM <Filename>.xlsx;

Re: Date Format issue

marcus_malinow — Wed, 19 Aug 2015 08:38:50 GMT

A combination of alt() and date#() should work.

Alt (date#(Date, 'MM/DD/YYYY'),

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

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

)

You can add additional date# functions in here to match any additional date formats you have in your source data.

Marcus

Re: Date Format issue

Anonymous — Wed, 19 Aug 2015 08:54:46 GMT

hi,

First Use interpretation function and use formatting function to make uniform date format.

still an issue.

Please post sample data and script

Regards

Neetha

Re: Date Format issue

qlikviewwizard — Wed, 19 Aug 2015 10:43:38 GMT

Hi All,

I tried with the below script. Not working.

Data:

load *, Date(Alt(Date#(Date, 'DD-MM-YYYY'), Date#(Date, 'MM-DD-YYYY'), Date#(Date, 'YYYY-DD-MM')), 'DD-MM-YYYY') as Date_Format2;

Load * , Date(Date#(Date, 'DD-MM-YYYY'), 'DD-MM-YYYY') As Date_Format1;

LOAD Date,sales INLINE

[

Date,sales

1/2/2009,400

2/13/2009,5000

2009/15/2,3000

3/17/2009,1

];

Re: Date Format issue

sasiparupudi1 — Wed, 19 Aug 2015 10:52:10 GMT

LOAD Date(Alt (date#(Date, 'MM/DD/YYYY'),date#(Date, 'YYYY/DD/MM'),date#(Date, 'MM/DD/YYYY')),'YYYY-MM-DD') as Date,sales

INLINE

[

Date,sales

1/2/2009,400

2/13/2009,5000

2009/15/2,3000

3/17/2009,1

];

Re: Date Format issue

marcus_malinow — Wed, 19 Aug 2015 10:53:49 GMT

That'd be because you haven't set up an alt condition for any of your date formats.....

You have formats of DD/MM/YYYY, MM/DD/YYYY and YYYY/DD/MM in your data and have set up conditions for DD-MM-YYYY, etc...

Re: Date Format issue

qlikviewwizard — Thu, 20 Aug 2015 00:35:56 GMT

Hi Marcus Malinow

I tried Alt function also, but is not working. could you provide the solution? Thanks.

Re: Date Format issue

marcus_malinow — Thu, 20 Aug 2015 08:24:46 GMT

See the attached

Re: Date Format issue

qlikviewwizard — Thu, 20 Aug 2015 14:18:57 GMT

Hi Marcus Malinow,

I got it. Many thanks.

Re: Date Format issue

qlikviewwizard — Thu, 20 Aug 2015 14:19:55 GMT

Hi Karthik,

Did you able to solve, please let us know. Thank you.