Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an years field that i need to format the year field
Try like:
KPI:
CrossTable(YearS,Number,4)
LOAD F1,
F2,
F3,
F4,
//[Yr 2015],
[P 00 - 2015],
[P 01 - 2015],
[P 02 - 2015],
[P 03 - 2015],
[P 04 - 2015],
[P 05 - 2015],
[P 06 - 2015],
[P 07 - 2015],
[P 08 - 2015],
[P 09 - 2015],
[P 10 - 2015],
[P 11 - 2015],
[P 12 - 2015],
[P 13 - 2015],
[P 14 - 2015]
FROM
(ooxml, embedded labels, header is 8 lines, table is Sheet1);
KPIFin:
Load
*,
Mid(YearS, 3,2) as NewYear
Resident KPI;
Drop table KPI;
You can use the subfield function to retrieve the part of the string between the first and second space: Text(subfield('P 00 2015',' ',2))
Try like:
KPI:
CrossTable(YearS,Number,4)
LOAD F1,
F2,
F3,
F4,
//[Yr 2015],
[P 00 - 2015],
[P 01 - 2015],
[P 02 - 2015],
[P 03 - 2015],
[P 04 - 2015],
[P 05 - 2015],
[P 06 - 2015],
[P 07 - 2015],
[P 08 - 2015],
[P 09 - 2015],
[P 10 - 2015],
[P 11 - 2015],
[P 12 - 2015],
[P 13 - 2015],
[P 14 - 2015]
FROM
(ooxml, embedded labels, header is 8 lines, table is Sheet1);
KPIFin:
Load
*,
Mid(YearS, 3,2) as NewYear
Resident KPI;
Drop table KPI;
Hi,
Another solution,
Left(Trim(PurgeChar('P 00 2015', 'P')),2)