Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Combine multiple rows

Hi,

I am trying to convert this table:

 

ordervalue_typedetail
1price100
1typeopen
1quantity1000
1sidebuy
2price200
2typeopen
2quantity5000
2sidebuy
3quantity10000
3sidebuy

into the following tables

   

orderpricetypequantityside
1100open5000buy
2110open5000sell
3 10000buy

&

   

ordervalue_typedetail
1buy_price100
2sell_price110

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!

3 Replies
MVP & Luminary
MVP & Luminary

Re: Combine multiple rows

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.


talk is cheap, supply exceeds demand
Partner
Partner

Re: Combine multiple rows

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;

Not applicable

Re: Combine multiple rows

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