Group fields into Single field in Qliksense

[LP27]

Hi Folks,

I have got months in many fields, I want to group all months into year field.

Example:-

[Table]:
LOAD
[Employee],
[Employee Name],
[OCT 2008],
[NOV 2008],
[DEC 2008],
[JAN 2009],
[FEB 2009],
[MAR 2009],
[APR 2009],
[MAY 2009],
[JUN 2009],
[JUL 2009],
[AUG 2009],
[SEP 2009],
[OCT 2009],
[NOV 2009],
[DEC 2009],
[JAN 2010],
[FEB 2010],
[MAR 2010],
[APR 2010],
[MAY 2010],
[JUN 2010],
[JUL 2010],
[AUG 2010],
[SEP 2010],

from <path>;

Now i want to group fields like this - All the months field into year. 

example:-

OCT 2008, Nov 2008, Dec 2008 as 2008

Jan 2009, Feb 2009------Dec 2009 as 2009

Jan 2010, Feb 2010------Dec 2010 as 2010

Like this I have to group all the months field into single year field.

Is there a possible way in Data Load editor in Qlik sense.

Could some one guide me ?

Thanks in advance,

LP27

[Vegar · Accepted solution]

The first step would be to use crosstable on your source

[Table]:
CROSSTABLE (YearMonth, Value, 2) LOAD
[Employee],
[Employee Name],
[OCT 2008],
[NOV 2008],
[DEC 2008],
[JAN 2009],
[FEB 2009],
[MAR 2009],
[APR 2009],
[MAY 2009],
[JUN 2009],
[JUL 2009],
[AUG 2009],
[SEP 2009],
[OCT 2009],
[NOV 2009],
[DEC 2009],
[JAN 2010],
[FEB 2010],
[MAR 2010],
[APR 2010],
[MAY 2010],
[JUN 2010],
[JUL 2010],
[AUG 2010],
[SEP 2010],
from <path>;

Then you need to format change the YearMont into Year and month.
Table2:
LOAD
[Employee],
[Employee Name],
YEAR(date#(YearMonth, 'MMM YYYY')) as Year,
date#(YearMonth, 'MMM YYYY') as YearMonth,
Value
Resident Table;
Drop table Table;
The final step would be to use Generic Load on this Table2.

Read how to use Generic Load here: https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470