topic Convert SAP month in QlikView

Convert SAP month

Fri, 21 Jan 2011 03:25:28 GMT

Hi,

I'm importing the month from SAP to QV.

The value for months are in Jan, Feb, Mar, ...

When i import to QV, i think it recognises it as text so when i concate with year, it's doing a normal sort a-z.

i tried >>> DATE#(MONTH&' '&YEAR, 'MMM/YY') as MONTH_YEAR, but it's not working well.

Can someone help?

Thanks.

deepakk — Fri, 21 Jan 2011 03:36:50 GMT

hi,

Try this expression...

date(date#(01 & '/' & 'Jan' & '/' & 2000,'DD/MMM/YYYY'),'DD/MM/YYYY')

Add a day 01 to your yearmonth and make it as Date.

I hope this helps.

hector — Fri, 21 Jan 2011 13:17:27 GMT

Hi, please see this answer

http://community.qlik.com/forums/p/39597/156035.aspx#156035

Rgds

PS. By the way maybe your error is this:

you have

DATE#(MONTH&' '&YEAR, 'MMM/YY') as MONTH_YEAR

but if you are concatenating 1 space, the second parameter is wrong, it has to be

DATE#(MONTH&' '&YEAR, 'MMM YY') as MONTH_YEAR

then you can do something like these:

num(month(DATE#(MONTH&' '&YEAR, 'MMM YY'))) return the numeric value of the month

Remember also, check the variable MonthNames, because 'Jan' is different from 'jan'