- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Converter texto jan,fev,mar... em numero 1 2 3
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.
- Tags:
- Group_Discussions
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=num(Month(date#(monthfield,'MMM')))
-Rob
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Rob for the explanation
Regards,