mapping the string 'jan' as a month?

[t_hylander]

I have a excel-file with a cell containing the month in text, but I cant get Qlikview to understand that it is a month.

Tried converting it manually to number and then makedate() but wasnt able to make it work.

If(månad = 'Jan',01,

      If(månad = 'Feb',02,

      If(månad = 'Mar',03,

      If(månad = 'apr',04,

      If(månad = 'maj',05,

      If(månad = 'Jun',06,

      If(månad = 'Jul',07,

      If(månad = 'aug',08,

      If(månad = 'Sep',09,

      If(månad = 'Okt',10,

      If(månad = 'Nov',11,

      If(månad = 'Dec',12

      ,0)))))))))))) AS månadsNummer, 

and the made a new load with resident on the precious table

tmpMonth:
Load
 month(makedate(År_Temp,månadsNummer) ) as månad_Temp
resident Invent;

Must be doing something wrong or in need of diffrent solution..

Thanks in advance.

[swuehl · Accepted solution]

If your month names variable is set to something like this:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

Then try

LOAD

     ...

     Month( Date#(månad,'MMM')) as månad_Temp,

    ....

FROM ...;

[#3]

Hi

it would be better to use a

T1:

LOAD * INLINE [

Month, Num

jan, 1

feb, 2

...

dec, 12];

and then map the month as number using

Map_Table:

Mapping load

Month,

Num

resident T1;

Then read your excel

LOAD

applymap('Map_Table', lower(Manad))     as Manad_Num   

          // you can use then this result to build date with makedate

...

FROM file.xls;

best regards

chris

[anandathome]

You can use:

Date(Date#(månad,'MMM'),'MM') as månad_Temp

OR

On your excel sheet, try to format that particular column to text.

and then imort it to qlikview.

[t_hylander]

That work smooth and easy, Thanks!

[t_hylander]

Already had it formatted as text but it didnt help..