topic Re: Extracting Month from Date present in XLS File in QlikView

Extracting Month from Date present in XLS File

Mon, 20 May 2013 13:40:54 GMT

Hi all,

I have exported MongoDB collection into an xls file and i am using this as the data source for my project.Below is one of the rows of xls file

XXXXX,"XXXXX","SUCCESS",2013-03-21T02:58:07Z,"XXXXXX"

Now I want to extract only the month from "2013-03-21T02:58:07Z".Please suggest the appropriate function to use in load script.

Re: Extracting Month from Date present in XLS File

Gysbert_Wassenaar — Mon, 20 May 2013 16:39:13 GMT

You can try: mid('2013-03-21T02:58:07Z',6,2) as Month

Re: Extracting Month from Date present in XLS File

MayilVahanan — Mon, 20 May 2013 18:16:55 GMT

Try like this

SubField('2013-03-21T02:58:07Z','-',2)

Hope it helps

Re: Extracting Month from Date present in XLS File

Tue, 21 May 2013 04:30:50 GMT

Thanks Gysbert it worked. Is there any another function available through which i can convert this number into month name say for March it will be 3, for April it will be 4. Actually I want to Display Month Name not the number.Can you please suggest something on this also.

Re: Extracting Month from Date present in XLS File

Tue, 21 May 2013 04:31:42 GMT

Thanks Mayil it also worked .Can you please see my reply to Gysbert answer.

Re: Extracting Month from Date present in XLS File

Sokkorn — Tue, 21 May 2013 04:37:08 GMT

Hi yomishra,

Try this one

Month(Left('2013-03-21T02:58:07Z',10)) ==> if doesn't work then use this

Month(Date(Date#(Left('2013-03-21T02:58:07Z',10),'yyyy-MM-dd')))

Regards,

Sokkorn

Re: Extracting Month from Date present in XLS File

Tue, 21 May 2013 04:40:25 GMT

Thanks Sokkorn!!!You answered exactly what i want .