Concatonate field value to field name

[hobanwashburne]

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?

[sunny_talwar]

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;

[sunny_talwar · Accepted solution]

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