Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
anthony_kinsell
Contributor

Date Format not working

Hi,

I'm importing some data from an excel sheet & creating a temp file. I'm then creating a 2nd temp file from the original temp file & trying to format the date as MMM-YYYY however I'm not getting anything in this field - can anyone tell me what's wrong with my script? It's the bit in bold & red below:

I'm thinking script is correct but for some reason Qlikview is not recognising the data as a date from the excel sheet?

CPI_Temp:

CrossTable(CPIDate, Data)

LOAD

*

FROM

(biff, embedded labels, table is [average monthly rents indexed$], filters(

Remove(Row, Pos(Top, 8)),

Remove(Row, Pos(Top, 7)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2))

));

CPI_Temp2:

LOAD

  CPIDate,

Date(CPIDate, 'MMM-YYYY') as YearMonth,

  Data

Resident CPI_Temp;

DROP Table CPI_Temp;

Sample of the results below:

CPIDateYearMonthData
-
28/02/2007-100.91
28/02/2009-103.89
28/02/2010-100.61
28/02/2011-102.74
28/02/2013-106.08
28/02/2014-106.08
29/02/2008-105.66
29/02/2012-104.93
1 Solution

Accepted Solutions

Re: Date Format not working

Hi,

Use MonthName(CPIDate) as MonthYear

Or

Go with Date#()

Like

Date(Date#(CPIDate,'DD/MM/YYYY'),'MMM-YYYY') as YearMonth


OR


MonthName(Date#(CPIDate,'DD/MM/YYYY')) as YearMonth

Great dreamer's dreams never fulfilled, they are always transcended.
2 Replies

Re: Date Format not working

Hi,

Use MonthName(CPIDate) as MonthYear

Or

Go with Date#()

Like

Date(Date#(CPIDate,'DD/MM/YYYY'),'MMM-YYYY') as YearMonth


OR


MonthName(Date#(CPIDate,'DD/MM/YYYY')) as YearMonth

Great dreamer's dreams never fulfilled, they are always transcended.
anthony_kinsell
Contributor

Re: Date Format not working

Thanks Max,

Much appreciated. This one worked:

Date(Date#(CPIDate,'DD/MM/YYYY'),'MMM-YYYY') as YearMonth


Why as a matter of interest do you have to use both Date & Date# - apologies if that is a silly question - relatively new to this type of thing.

Regards

Anthony

Community Browser