Converter texto jan,fev,mar... em numero 1 2 3

[#1]

Boa noite

Como posso converter um campo onde tenho apenas os nomes dos meses jan,fev,mar,abr,.... em numero 1,2,3,4 ... no script.

[rwunderlich · Accepted solution]

=num(Month(date#(monthfield,'MMM')))

-Rob

[Anonymous]

Hi twister8,

Maybe this can help:

=Date(Date#('jan', 'MMM'), 'M')

Date# converts your text in date format (in your case just replace 'jan' by the name of your field), and then you use date again to return the month as a number.

Hope this helps.

[#4]

Karla Reis thaks so much for answer me 🙂

The first step its ok

Load *,

Date(Date#(Mês, 'MMM'), 'M') as MES_NUMERO;

LOAD Mês,

    Coluna

FROM

testetextomes.xlsx

(ooxml, embedded labels, table is Plan1)

where 'MES_NUMERO' >= num(month(today(0));

The second step

I need show just data when the MES_NUMERO its >= that month actual, i tryed this code but doesnt work, can you help me ?

[#5]

PERFECT :

LOAD Mês,

     Coluna,

     num(Month(date#(Mês,'MMM'))) as MES_NUMERO

FROM

testetextomes.xlsx

(ooxml, embedded labels, table is Plan1)

where num(Month(date#(Mês,'MMM'))) > num(month(today(0)));

[rwunderlich]

Karla,. I don't think your solution will work in this case.  Date() is a formatting function, and will not change the underlying value -- which is what is used for numeric comparisons.

Date(Date#('jan', 'MMM'), 'M') = -363

therefore

Date(Date#('jan', 'MMM'), 'M') <> 1

-Rob

[Anonymous]

Hi Rob,

Previously, when I suggested the expression, I was focused on returning the number of the month. I didn't consider the possibility of using it in a condition. After reading your comment,  I tried to use it in a comparison and it didn't work (I got false as a result)

=If(Date(Date#('jan', 'MMM')) =1, 'true', 'false')

Like you said, Qlik returns the number format, but internally it keeps the value in a different one. It's a little bit misleading. Thank your for clarifying the concept, I'll keep this in mind the next time.

Regards,

Karla

[rwunderlich]

To be specific (and a bit pedantic ), Date() in this format does not return the number of the month. Date() returns a Dual. A Dual has both a string representation ("1" in this case) and a numeric value (-363). Whether the string or numeric value is used depends on the context. If used in listbox, the string value will be used. If used in a "=" comparison, the numeric value is used. You can extract one or the other value using num() or text().

=Date(Date#('jan', 'MMM'), 'M') = 1  //false

=text(Date(Date#('jan', 'MMM'), 'M')) = 1  //true

=num(Date(Date#('jan', 'MMM'), 'M')) = -363  //true

Unfortunately, only quoting what's to the right of the = does not infer using string (I wish).

=Date(Date#('jan', 'MMM'), 'M') = '1'  //false

But using a string operator (like) will infer using the string value.

=Date(Date#('jan', 'MMM'), 'M') like '1'    // true

The behavior of Dual() is a bit confusing at first. But it's very useful and well worth understanding.

-Rob

[#9]

Talvez isso..

if(Mês=jan,'1',

if(Mês=Fev,'2',

if(Mês=Mar,'3',

if(Mês=Abr,'4',

if(Mês=Mai,'5',

if(Mês=Jun,'6',

if(Mês=Jul,'7',

If(Mês=Ago,'8',

if(Mês=Set,'9',

if(Mês=Out,'10',

if(Mês=Nov,'11',

if(Mês=Dez,'12',

)))))))))))) as Mês_Num,

[Anonymous]

Thanks Rob for the  explanation

Regards,