Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jakepro716
Contributor
Contributor

Extracting the Date/Year from columns?

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. 

EXCEL_KXFPtl0nt3.png

Labels (1)
1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

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;

Lisa_P_0-1651543414997.pngLisa_P_1-1651543431708.png

 

View solution in original post

1 Reply
Lisa_P
Employee
Employee

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;

Lisa_P_0-1651543414997.pngLisa_P_1-1651543431708.png