topic mapping the string 'jan' as a month? in QlikView

mapping the string 'jan' as a month?

t_hylander — Fri, 28 Jun 2013 10:03:50 GMT

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.

Re: mapping the string 'jan' as a month?

swuehl — Fri, 28 Jun 2013 10:10:52 GMT

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 ...;

Re: mapping the string 'jan' as a month?

Fri, 28 Jun 2013 10:11:02 GMT

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

Re: mapping the string 'jan' as a month?

anandathome — Fri, 28 Jun 2013 11:46:32 GMT

You can use:

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

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

and then imort it to qlikview.

Re: mapping the string 'jan' as a month?

t_hylander — Mon, 01 Jul 2013 08:18:29 GMT

That work smooth and easy, Thanks!

Re: mapping the string 'jan' as a month?

t_hylander — Mon, 01 Jul 2013 08:19:11 GMT

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