topic Re: Transform table in load script, turning column values into row values depending on Key in App Development

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

Usafza — Wed, 03 Jan 2024 14:32:44 GMT

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

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

PrashantSangle — Thu, 04 Jan 2024 04:35:40 GMT

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

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

Usafza — Thu, 04 Jan 2024 09:39:36 GMT

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.

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

marcus_sommer — Fri, 05 Jan 2024 09:27:35 GMT

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.

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

PrashantSangle — Fri, 05 Jan 2024 09:31:25 GMT

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

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

Usafza — Fri, 05 Jan 2024 09:41:53 GMT

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

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

Saravanan_Desingh — Sat, 06 Jan 2024 16:56:12 GMT

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

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

PrashantSangle — Sat, 06 Jan 2024 17:15:17 GMT

Awesome.. like it