Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am pulling data that has been formatted in the following way:
COMP| YEAR| PNS01| PNS02| PNS03...
1 | 2015| 10,000| 35,000| 27,000
2 | 2015| 12,000| 32,000| 25,000
Where PNS01 represents the planned net sales for fiscal period 1 of 2015 of company 1. Ultimately I reload as a crosstable and convert the fiscal periods to calendar dates, but one of interim steps involves renaming PNS01 to 201501. In other terms:
PNS01 as 201501
or
PNS01 as <Value of field YEAR>01
Does anyone know of a way to do this without hard coding the year?
Sample Script:
Table:
LOAD * Inline [
COMP, YEAR, PNS01, PNS02, PNS03
1, 2015, 10000, 35000, 27000
2, 2015, 12000, 32000, 25000
];
Temp:
CrossTable(Month, Data, 2)
LOAD *
Resident Table;
DROP Table Table;
FinalTable:
LOAD COMP,
YEAR,
Date#(YEAR & Replace(Month, 'PNS', ''), 'YYYYMM') as YearMonth,
Data
Resident Temp;
DROP Table Temp;
Output in Table Box Object
What if you convert this after you have applied the CrossTable Load
Table:
CrossTable(Month, Data, 2)
LOAD *
FROM xyz
FinalTable:
LOAD COMP,
YEAR,
Date#(YEAR & Replace(Month, 'PNS', ''), 'YYYYMM') as YearMonth,
Data
Resident Table;
DROP Table Table;
Sample Script:
Table:
LOAD * Inline [
COMP, YEAR, PNS01, PNS02, PNS03
1, 2015, 10000, 35000, 27000
2, 2015, 12000, 32000, 25000
];
Temp:
CrossTable(Month, Data, 2)
LOAD *
Resident Table;
DROP Table Table;
FinalTable:
LOAD COMP,
YEAR,
Date#(YEAR & Replace(Month, 'PNS', ''), 'YYYYMM') as YearMonth,
Data
Resident Temp;
DROP Table Temp;
Output in Table Box Object