topic Re: Date conversion in QlikView

Date conversion

andyrebs — Mon, 23 Mar 2015 10:00:05 GMT

Hello,

I have two field, the first [delivery year]

(2015) and the second [month/day delivery] (313) as follow

delivery year	month/day delivery
2015	313
2015	314
2015	314
2015	316

How can I load in the script a complete date filed with the format 'DD/MM/YYYY'

Thank you

Best regards.

Andrea

Re: Date conversion

amit_saini — Mon, 23 Mar 2015 10:01:24 GMT

Hi,

Date(Your_Date_Field,'DD/MM/YYYY') as Date

Thanks,

Re: Date conversion

Mon, 23 Mar 2015 10:03:19 GMT

Try like below,

date(makedate([delivery year],left([month/day delivery],1),right([month/day delivery],2)),'DD/MM/YYYY') as date

Re: Date conversion

senpradip007 — Mon, 23 Mar 2015 10:05:05 GMT

Try like

MakeDate([delivery year], left([month/day delivery], 1), right([month/day delivery], 2)) as Date

Re: Date conversion

andyrebs — Mon, 23 Mar 2015 10:33:43 GMT

Thank you All and thank you Harshal,

your expression works properly, but I have seen that the field [month/day delivery] could be also with 4 digits and not only 3,

eg. 03 november as 1103

03 march as 303

How can I manage this difference?

Thanks!!

Re: Date conversion

Mon, 23 Mar 2015 10:47:52 GMT

Hi,

you need to create separate fields like below,

if(len([month/day delivery])>3,left([month/day delivery],2),left([month/day delivery],1)) as [Month_month/day delivery]

right([month/day delivery],2) as [Day_month/day delivery]

Then create,

New_TBL:

Load

date(makedate(YearField,[Month_month/day delivery],[Day_month/day delivery]),'DD/MM/YYYY') as Date

resident Old_Table;

Re: Date conversion

Mon, 23 Mar 2015 11:40:57 GMT

Hi Andrea,

please see attached, can use the below to give you this

Date(Date#([delivery year] & If(len([month/day delivery])<4, 0 & [month/day delivery], [month/day delivery]),'YYYYMMDD'),'DD/MM/YYYY') As date

hope that helps

Joe

Re: Date conversion

andyrebs — Mon, 23 Mar 2015 13:15:08 GMT

Thank you Joe!

Re: Date conversion

Mon, 23 Mar 2015 13:19:35 GMT

no worries glad to help