topic Re: Timestamp field issue in QlikView

Timestamp field issue

gayatri7 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a field which has timestamp value '4/8/2010 2:43:29 PM'. I wanted to get the month & year combination out of all dates. I have tried both the logics but still not getting the desired result.

1. date(monthstart(date#(ClaimSubmitDate, 'mm/dd/yyyy hh:mm:ss tt')), 'MMM YYYY') as Claim_Month,

2. date(monthstart(timestamp#(ClaimSubmitDate, 'mm/dd/yyyy hh:mm:ss tt')), 'MMM YYYY') as Claim_Month,

Could anyone please help me to understand if I am missing anything.

Attaching the sample qvw and data file

Thanks

Re: Timestamp field issue

sunny_talwar — Fri, 18 May 2018 11:19:21 GMT

May be try this

Date(MonthStart(TimeStamp#(ClaimSubmitDate, 'M/D/YYYY h:mm:ss TT')), 'MMM YYYY') as Claim_Month,

few things to note

1) M is for Month and m is for minute... in TimeStamp#() you use m for month

2) It seems that there are two spaces between Date and time portion... if that is true, make sure to use two spaces within TimeStamp#() interpretation or you can try this

Date(MonthStart(Date#(SubField(ClaimSubmitDate, ' ', 1), 'M/D/YYYY')), 'MMM YYYY') as Claim_Month,

Re: Timestamp field issue

sunny_talwar — Fri, 18 May 2018 11:22:13 GMT

Based on the sample file you provided... this should work because your date is already understood as date by QlikView... you don't need an interpretation function here (Date#() or TimeStamp#())

Date(MonthStart(ClaimSubmitDate), 'MMM YYYY') as Claim_Month,

Re: Timestamp field issue

gayatri7 — Sat, 26 May 2018 04:06:56 GMT

Thank u so much!! U r always a Saviour!!