topic Problems while converting date format in QlikView

Problems while converting date format

Mon, 29 Jul 2013 21:25:55 GMT

Hi,

could somebody pls take a look into the following qvw and Excel file and tell me, why QV does not interpret the date format in the right way?!

I tried so many possibilities in the script - nothing worked...

background: I need to convert the field 'Dateformat' into the format of DateMonth and DateYear (MM/YYYY) for my mastercalendar.

Thanks,

Dave

Re: Problems while converting date format

shree909 — Mon, 29 Jul 2013 21:33:29 GMT

Hi use this expression in the script to calculate the dates

=Date(Date#(SubField(Date,' ',-2),'MM/DD/YYYY'),'DD/MM') AS DateMonth for date and month

=Date(Date#(SubField(Date,' ',-2),'MM/DD/YYYY'),'DD/YYYY') As DateYear

=Date(Date#(SubField(Date,' ',1),'MM/DD/YYYY'),'DD/MM') AS DateMonth for date and month

=Date(Date#(SubField(Date,' ',1),'MM/DD/YYYY'),'DD/YYYY')

replace date with [Interaction Created Timestamp] field..

Re: Problems while converting date format

rwunderlich — Mon, 29 Jul 2013 21:47:57 GMT

I would approach it like this. [The Interaction Created Timestamp] is being correctly interpreted. You'll need to floor() that field to get just the date portion. Then you can use a preceding load to create additional fields from the Date.

Tickets:

LOAD

Date(MonthStart(Date),'MMM-YYYY') as MonthYear,

Month(Date) as DateMonth,

Year(Date) as DateYear

;

LOAD

'Tickets' as Source,

Date(Floor([Interaction Created Timestamp])) as Date,

[Interaction Created Timestamp] as Dateformat,

'ITSM Created' as DateFlag

FROM

source.xlsx

(ooxml, embedded labels, table is Report);

-Rob

Re: Problems while converting date format

Mon, 29 Jul 2013 21:51:46 GMT

Works perfectly! Thank you so much!