Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

t_hylander
New Contributor III

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.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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

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

5 Replies
MVP
MVP

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

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

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

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
Contributor

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

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
New Contributor III

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

That work smooth and easy, Thanks!

t_hylander
New Contributor III

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

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

Community Browser