topic Re: date format conversions in QlikView

date format conversions

Anonymous — Thu, 25 Jan 2018 10:59:22 GMT

Hi All,

I am trying to convert the following format:

Date : Sep 2017 to 08-2017

Any suggestions please?

Regards

Re: date format conversions

YoussefBelloum — Thu, 25 Jan 2018 11:18:12 GMT

hi,

try this on the script:

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

Re: date format conversions

Anonymous — Thu, 25 Jan 2018 11:44:22 GMT

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

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

Regards

Re: date format conversions

bhargav_bhat — Thu, 25 Jan 2018 11:47:20 GMT

Hi Rudra,

Maybe this will work

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

Regards,

Bhargav

Re: date format conversions

Anonymous — Thu, 25 Jan 2018 11:51:58 GMT

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

Regards

Re: date format conversions

bhargav_bhat — Thu, 25 Jan 2018 11:54:41 GMT

Hi Rudra,

Sep 2017 is in Date format or text format

Regards,

Bhargav

Re: date format conversions

Anonymous — Thu, 25 Jan 2018 11:58:06 GMT

Bhargav , its in Text Format

Regards

Re: date format conversions

bhargav_bhat — Thu, 25 Jan 2018 12:04:52 GMT

Hi Rudra,

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

Regards,

Bhargav

Re: date format conversions

bhargav_bhat — Thu, 25 Jan 2018 12:10:32 GMT

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)

Re: date format conversions

Anonymous — Thu, 25 Jan 2018 12:12:30 GMT

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

Re: date format conversions

maxgro — Thu, 25 Jan 2018 12:13:01 GMT

maybe you have to ltrim some spaces

Re: date format conversions

Anonymous — Thu, 25 Jan 2018 12:27:03 GMT

Excellent Bhargav, your left trim with Youssef's Code worked.

Thanks so much.

Re: date format conversions

bhargav_bhat — Thu, 25 Jan 2018 12:58:56 GMT

Hi Rudra,

Can you please mark it as helpful if it answered your question

Regards,

Bhargav

Re: date format conversions

pradosh_thakur — Thu, 25 Jan 2018 13:24:55 GMT

Hi Rudra

its great that you got the answer but you can try this in the future if you are not really sure about the space in dates.

for example

' SEP 2017' :- It has some leading space as well as one space between sep and 2017

'SEP 2017 ' :- It has some trailing space as well as two spaces between sep and 2017

what i generally do is

date#(purgechar(date_field,' '),date format withoutspace)

for example

DATE(date#(purgechar(date_field,' '),'MMMYYYY'))

Hope you will find this helpful .

regards

Pradosh

Re: date format conversions

Anonymous — Thu, 25 Jan 2018 13:45:14 GMT

Nice one Pradosh, appreciate your suggestion.

Regards

Re: date format conversions

Anonymous — Thu, 25 Jan 2018 13:47:31 GMT

Thank you very much Massimo

Regards