Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlikview community members,
Question:
How do I change a text monthname (3 leters) to a month number?
So how does Feb become 02?
Thanks!
Dennis.
Hi
See the attached file.
Hope that helps
Hi,
If you have 12 month in letters, you can use a simple map table to transform to number :
first load a table like this for 12 month with the name and coresponding number :
Mapping
MAP_MONTH:
LOAD * INLINE [
Letter, Number
Jan, 1
Feb, 2
Mar, 3
Apr, 4
];
then use it when loading your data :
load ...,
ApplyMap('MAP_MONTH',Month) as No_Month,
Yes that was the solution I have now, but there must be a way to let Qlikview recognize the months when using:
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
In the script. If not I will use this 🙂
Hi,
You can try by setting a variable
=Num(Month('02/02/2012')) returns 2
Thanks Manisha but my date field looks like '2 Feb 2012' and is a text field.
I already used :
KeepChar(datefield, 'JanFebMarAprMayJunJulAugSepOctNovDec') as Month,
So my field Month has now 'Feb' as text. But Qlikview does not recognize it as a month.
Try this:
=date(date#(MyDateString,'dd MMM yyyy'),'dd-MM-yyyy')
=date(date#(''12 feb 2012,'dd MMM yyyy'),'dd-MM-yyyy') returns a date formatted as 12-02-2012
Thansk G.
But:
=date(date#('12 feb 2012','dd MMM yyyy'),'dd-MM-yyyy') Returns: -
See attched file.
Hi
See the attached file.
Hope that helps
Hi,
Maybe you could try this one.
Date(Date#('Feb','MMM'),'MM')
if the format is this '2 Feb 2012'
you could try this
Date(Date#(mid('2 Feb 2012',3,3),'MMM'),'MM')
Regards,
Janzen
Thank you all, I used Mayil's solution.