Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
sr_differentia
New Contributor III

date format conversions

Hi All,

I am trying to convert the following format:

Date :  Sep 2017 to 08-2017 

Any suggestions please?

Regards

1 Solution

Accepted Solutions
MVP
MVP

Re: date format conversions

maybe you have to ltrim some spaces

1.png

15 Replies
YoussefBelloum
Esteemed Contributor

Re: date format conversions

hi,

try this on the script:

num(month(date#(left(Date,3),'MMM')))&'-'&right(Date,4) as Date

sr_differentia
New Contributor III

Re: date format conversions

Nice one Youssef, its picking the 2017 field but not picking the month field.

Extract is looking like : -2017 (Month is not picked)

Regards

bhargav_bhat
Contributor II

Re: date format conversions

Hi Rudra,

Maybe this will work

Date(Date#('Sep 2017','MMM YYYY'),'MM-YYYY')

Regards,

Bhargav

sr_differentia
New Contributor III

Re: date format conversions

Hi Bhargav, tried that one, but its not working.

Regards

bhargav_bhat
Contributor II

Re: date format conversions

Hi Rudra,

Sep 2017 is in Date format or text format

Regards,

Bhargav

sr_differentia
New Contributor III

Re: date format conversions

Bhargav , its in Text Format

Regards

bhargav_bhat
Contributor II

Re: date format conversions

Hi Rudra,


The above expression should work .Can you please share Sample data if possible


Regards,

Bhargav

bhargav_bhat
Contributor II

Re: date format conversions

You can also try this expression if above expression does'nt work

Pick(Match(Left(FieldName,3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

,01,02,03,04,05,06,07,08,09,10,11,12) & '-' & right(FieldName,4)

sr_differentia
New Contributor III

Re: date format conversions

Hi Bhargav, i can explain the scenario:

* I got no date field in source data.

* I picked the date part from source data excel file names(File name has extension -sep 2017.xls )

* Used text between to extract that part (Sep 2017)

* Now i am trying to convert that Sep 2017 field value to 08-2017 to mark it as date field in all the excel source files.

Regards

Community Browser