topic Re: Use timestamp notation to make monthname? in QlikView

Use timestamp notation to make monthname?

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Use timestamp notation to make monthname?

Anonymous — Thu, 08 Dec 2016 14:09:37 GMT

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.

Re: Use timestamp notation to make monthname?

MK9885 — Thu, 08 Dec 2016 14:10:10 GMT

month(YourDateField)

as Month

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

Do you have your Date Field?

Re: Use timestamp notation to make monthname?

sunny_talwar — Thu, 08 Dec 2016 14:11:15 GMT

May be this:

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

Re: Use timestamp notation to make monthname?

rupamjyotidas — Thu, 08 Dec 2016 14:58:27 GMT

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

Re: Use timestamp notation to make monthname?

MarcoWedel — Thu, 08 Dec 2016 23:03:08 GMT

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

Re: Use timestamp notation to make monthname?

Anil_Babu_Samineni — Fri, 09 Dec 2016 00:30:23 GMT

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?

Re: Use timestamp notation to make monthname?

Fri, 09 Dec 2016 07:10:12 GMT

This works, thanks!

Re: Use timestamp notation to make monthname?

Fri, 09 Dec 2016 07:10:46 GMT

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