Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Usafza
Contributor II
Contributor II

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

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

Labels (1)
2 Solutions

Accepted Solutions
Usafza
Contributor II
Contributor II
Author

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

View solution in original post

Saravanan_Desingh

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
]
;

commqv003.png

View solution in original post

7 Replies
PrashantSangle

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

 

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Usafza
Contributor II
Contributor II
Author

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Usafza
Contributor II
Contributor II
Author

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

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
]
;

commqv003.png

PrashantSangle

Awesome.. like it

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂