Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Use timestamp notation to make monthname?

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

1 Solution

Accepted Solutions
sunny_talwar

May be this:

Month(Date#(If(WildMatch(Timestamp, '*T*'), mid(Timestamp, 5, 2), mid(Timestamp, 6, 2)), 'MM')) as MonthName

View solution in original post

8 Replies
payalgosar
Creator II
Creator II

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.

MK9885
Master II
Master II

    month(YourDateField)as Month         

Should give you Month Names (Jan, Feb...)

Do you have your Date Field?

sunny_talwar

May be this:

Month(Date#(If(WildMatch(Timestamp, '*T*'), mid(Timestamp, 5, 2), mid(Timestamp, 6, 2)), 'MM')) as MonthName

rupamjyotidas
Specialist
Specialist

The value sin your script is returning in TEXT String. You need to convert them to Num. Using Date#().

MarcoWedel

Hi,

maybe one solution could be:

QlikCommunity_Thread_242685_Pic1.JPG

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

Anil_Babu_Samineni

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?

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

This works, thanks!

Not applicable
Author

Thanks everyone for your quick help and willingness to share your knowledge with me! Thanks!