Combine multiple rows

[#1]

Hi,

I am trying to convert this table:

order	value_type	detail
1	price	100
1	type	open
1	quantity	1000
1	side	buy
2	price	200
2	type	open
2	quantity	5000
2	side	buy
3	quantity	10000
3	side	buy

into the following tables

order	price	type	quantity	side
1	100	open	5000	buy
2	110	open	5000	sell
3			10000	buy

&

order	value_type	detail
1	buy_price	100
2	sell_price	110

What is the best way to do this?

I tried GENERIC load as per the instruction here - Use cases for Generic Load | Qlikview Cookbook - but that created a lot of tables because there lot more value_types that the ones listed above and eventually crashed the app when I used the loop to get rid of the extra tables.

Is there a way to do it using a concatenate & group by ?

Thanks in advance!

[Gysbert_Wassenaar]

and eventually crashed the app when I used the loop to get rid of the extra tables

There's no need to get rid of the tables as explained in this blog post: The Generic Load

See attached example.

Note, your requirements are very hard to meet. It's quite difficult to turn a 'buy' into a 'sell' and make 200 appear as 110. I opted for modifying the source table.

[phaneendra_kunc]

U can try this to combine your tables.. i just concatenated the data, but after coming up wiht single table you should do a sum and group by to reduce no of rows..

Generic

Fact:

LOAD * INLINE [

    order, value_type, detail

    1, price, 100

    1, type, open

    1, quantity, 1000

    1, side, buy

    2, price, 200

    2, type, open

    2, quantity, 5000

    2, side, buy

    3, quantity, 10000

    3, side, buy

];

for i = 0 to NoOfTables()

Tables:

Load TableName($(i)) as TableName AutoGenerate 1 where TableName($(i)) <> 'Tables' ;

next i

FinalFact:

Load 1 as DUMMY AutoGenerate 1; 

For j = 0 to NoOfRows('Tables')-1

let vTable = peek('TableName',$(j),'Tables');

Concatenate(FinalFact)

Load * Resident $(vTable);

DROP  Table $(vTable);

NEXT j

DROP Field DUMMY;

[#4]

Thanks Guys! The generic load approach worked and I opted to not delete the additional tables.