2 Replies Latest reply: May 7, 2014 1:09 PM by Anthony Kinsella RSS

    Date Format not working

    Anthony Kinsella

      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

      [C:\Documents and Settings\anthony\Desktop\Copy of Block B analysis Q1 2014.xls]

      (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