topic Re: Datetime Format Convertion in QlikView

Datetime Format Convertion

techvarun — Fri, 13 Feb 2015 13:48:19 GMT

Hi Qlixperts

I have three kind of date formats in a same aplication and the data sources are different

1. 201513 --- means 13th Week of 2015

2.2015-03-2015

3. 03/30/2015

I need to convert all these dates into a single format (Format number 3 03/30/2015)

can anyone help meout to change the format

in the format 1 (201513) basically i need to show the Monday's date of 13th month of the year

Thanks

Varun

Re: Datetime Format Convertion

Michiel_QV_Fan — Fri, 13 Feb 2015 13:56:48 GMT

Wel you need date#() function to set the format and then use Date() function to make them the same.

Also do you need to make use of makedate to add in the day numbers.

1. 201513 --- means 13th month of 2015

You need 4 weeks periods to get an 13th period in your year. A 13 month doesn't exist so cannot be created.

I this date a 13th period in the year?

2.2015-03-2015

date(makedate(date(date#(<your field>, 'YYYY-MM-YYYY'),'YYYY'), date(date#(<your field>, 'YYYY-MM-YYYY'),'MM'), monthend(date(date#(<your field>, 'YYYY-MM-YYYY'),'YYYY-MM'))), 'DD-MM-YYYY'') as Date

3. 03/30/2015

date(date#(<your field>, 'DD/MM/YYYY'), 'DD-MM-YYYY')

Re: Datetime Format Convertion

techvarun — Fri, 13 Feb 2015 14:01:38 GMT

//1. 201513 --- means 13th month of 2015//

Mybad it is 13th week of 2015

Thanks

Varun

Re: Datetime Format Convertion

Colin-Albert — Fri, 13 Feb 2015 14:06:27 GMT

There aren't 13 months in a year, so the standard date format functions date#() or makedate() will not work.

You will need to provide your own table that maps the year/period to a date.

You can use alt(expr1, expr2, exprN) to nest multiple expressions together, alt() will use the result from the first expression that returns a valid numeric value. As QlikView dates are dual values a correctly formatted date is seen as a number.

Re: Datetime Format Convertion

Michiel_QV_Fan — Fri, 13 Feb 2015 14:08:23 GMT

DATE(date#(makeweekdate(left(<your field>, 4), right(<your field>, 2)), 'YYYYMDD'), 'DD-MM-YYYY')

Re: Datetime Format Convertion

techvarun — Fri, 13 Feb 2015 14:09:10 GMT

Hello Albert

Thanks for your reply

It was a type (edited now)....

It is13th week of the year

Thanks

Varun