Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
CPIDate | YearMonth | Data |
- | ||
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 |
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
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
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