App Development

[jakepro716]

I have a dataset similar to the one below and am trying to extract the Year from the column header and generate a row for each year. Not sure of the best approach for this.

Currently I tried a crosstable, then created a calculated column to extract the Year and the rest of the header as 2 columns (Year cand Type), but would now need to pivot that to make the Type column back into headers and am not sure if that is possible. 

I then want to add more columns that are X/Y, X/Z, etc. 

[Lisa_P · Accepted solution]

This is possible using the Generic prefix.

After creating your table from Crosstable prefix, you use this a resident load source to Split out your Year and Type  using Subfield, then just simply add the Generic prefix before that load statement and drop the original table. The layout will look odd, but it will give you the result you are looking for.

CrossTable:
CROSSTABLE ([Year Type],[Amount],2)
LOAD .....
;

Generic
Load
Program,
[Account Number],
SubField([Year Type], ' ', 1) as Year,
SubField([Year Type], ' ', 2) as Type,
Amount
Resident CrossTable;

Drop Table CrossTable;