Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
t_hylander
Creator
Creator

mapping the string 'jan' as a month?

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

5 Replies
swuehl
MVP
MVP

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

Not applicable

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
Creator
Creator

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
Creator
Creator
Author

That work smooth and easy, Thanks!

t_hylander
Creator
Creator
Author

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