3 Replies Latest reply: May 11, 2016 3:11 PM by Aravind Sasidharan RSS

    Combine multiple rows

    Aravind Sasidharan

      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!

        • Re: Combine multiple rows
          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.

          • Re: Combine multiple rows
            Phaneendra Kunche

            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;

            • Re: Combine multiple rows
              Aravind Sasidharan

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