Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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,
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,
Thank u so much!! U r always a Saviour!!