Transform table in load script, turning column values into row values depending on Key

[Usafza]

I want to turn this:
Test:
Load
*
Inline
[
Key, Department, Year
1, a, 1999
1, b, 2000
1, a, 2001
2, b, 2000
3, c, 1987
3, d, 1990
3, a, 2000
]
;

Into this:

Key     Dep1     Dep2     Dep3

1           a            b             a     

2           b            -              -     

3           c            d             a 

How can I achieve this output in the load editor?  I don't think CrossTable or Generic will work.

Thank you

[PrashantSangle]

what do you want to do with year field in this output??

[Usafza]

The Year field is there to show the order in which a case goes through departments. I dont need it in the final output, but it can be used to order the input table or in another way as a tool to determine the order for departments.

[marcus_sommer]

The Generic Load - Qlik Community - 1473470 could be used - take a closer look on the second part of the posting in which is shown how to join all tables together again.

[PrashantSangle]

Generic load not going to work for him, check his end output field name is different.

I think hierarchy() or hierarchyBelongsto() function will help him.

Regards,

Prashant Sangle

[Usafza · Accepted solution]

I ended up finding a solution. Not sure if it is the most efficient one but it did the job. For anyone with the same problem: 
I started with ordering the values in the table by Key and Year And subsequently introduced an ordering for each row based on its Key in a new load:
      if(Key<>previous(Key),1,peek('Order')+1) as Order
And just concatenating it with Department for the column names:
     'Department ' & Order as Attribute
Before doing the Generic load
   Generic Load Key,
       Attribute,
       Department

[Saravanan_Desingh · Accepted solution]

Try this,

Test:
Generic
LOAD Key,
    'Dep'&Autonumber(RowNo(),Key) as Col,
    Department
Inline
[
Key, Department, Year
1, a, 1999
1, b, 2000
1, a, 2001
2, b, 2000
3, c, 1987
3, d, 1990
3, a, 2000
]
;

[PrashantSangle]

Awesome.. like it