Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
Im having a small problem with the creation of monthnames in my Qlikview file.
The data im loading into Qlik to make a date (Year, month number and day number) is notated as
a timestamp like this: 20160920T223243.000+0200
I worte my script like this to extract the month and day numbers from the Timestamp:
-------------------------------------------------------------------------------------------------------------------------------------
If(WildMatch(Timestamp, '*T*'), mid(Timestamp, 5, 2), mid(Timestamp, 6, 2)) as Month_nr,
If(WildMatch(Timestamp, '*T*'), mid(Timestamp, 7, 2), mid(Timestamp, 9, 2)) as Day,
-------------------------------------------------------------------------------------------------------------------------------------
It all works as numbers with this piece of script, but I can't change the month numbers in to their names
like "jan, feb etc."
Unfortunately I can't attach a qlikview file due to secrecy.
Thanks for your help in advance!
Ivo Geel
May be this:
Month(Date#(If(WildMatch(Timestamp, '*T*'), mid(Timestamp, 5, 2), mid(Timestamp, 6, 2)), 'MM')) as MonthName
Hi Ivo,
You can create an inline table with the MonthNum and Month field.
Use applymap() function with Month_nr field and get the name.
month(YourDateField) | as Month |
Should give you Month Names (Jan, Feb...)
Do you have your Date Field?
May be this:
Month(Date#(If(WildMatch(Timestamp, '*T*'), mid(Timestamp, 5, 2), mid(Timestamp, 6, 2)), 'MM')) as MonthName
The value sin your script is returning in TEXT String. You need to convert them to Num. Using Date#().
Hi,
maybe one solution could be:
LOAD *,
Year(Timestamp) as Year,
Month(Timestamp) as Month,
Num(Month(Timestamp)) as Month_nr,
Day(Timestamp) as Day,
Hour(Timestamp) as Hour,
Minute(Timestamp) as Minute,
Second(Timestamp) as Second;
LOAD Timestamp(Timestamp#(Left(Timestamp,8)&' '&Mid(Timestamp,10,10), 'YYYYMMDD hhmmss.fff')+If(Mid(Timestamp,20,1)='-',-1,1)*Interval#(Right(Timestamp,4),'hhmm')) as Timestamp
INLINE [
Timestamp
20160920T223243.000+0200
20160921T223243.000-0330
20160922T223243.000+0530
20160102T012345.000+0000
20160203T012446.000+0000
20160304T012547.000+0000
20160405T012648.000+0000
20160506T012749.000+0000
20160607T012850.000+0000
20160708T012951.000+0000
20160809T013052.000+0000
20160910T013153.000+0000
20161011T013254.000+0000
20161112T013355.000+0000
20170113T013456.000+0000
];
hope this helps
regards
Marco
Nice one, But why dates are not frequently showing
1st one should be -- 09/20/2016
2nd one should be -- Why Day is showing 22nd instead of 21
3rd one should be -- 09/23/2016 -- Why Day is showing 22nd instead of 23
Can you confirm on the same. Does it make sense?
This works, thanks!
Thanks everyone for your quick help and willingness to share your knowledge with me! Thanks!