6 Replies Latest reply: Apr 24, 2017 11:33 AM by Antonio Mancini RSS

    Two rows into Single Row

    scotly victor

      Hi

       

      I have Scenario that i need to combine two rows into single row

       

      Date            account id  Bank   Amount

      01/02/2016  114AE           SBI         400

      01/02/2016  114AE          BOI         -400

      02/03/2016 122DG          Axis        700

      02/03/2016 122DG         Canara   -700

       

      Desired Output:

       

      Date            account id    Source Name  Recipent Name  source Amount recipent Amount

      01/02/2016  114AE           BOI                SBI                       -400               400

      02/03/2016 122DG           Canara             Axis                       -700             700

       

      Can anyone Help?

        • Re: Two rows into Single Row
          omar bensalem

          Maybe like this :

           

          table:

          load Date,"account id",Amount as  RecipientAmount, Bank as Recipient

          where Bank='SBI' ;

           

          load * Inline [

           

          Date ,           account id,  Bank,   Amount

          01/02/2016,  114AE ,          SBI ,        400

          01/02/2016,  114AE,          BOI,         -400

          ];

           

          left join(table)

           

          load Date,"account id",Amount as  SourceAmount, Bank as Source

          where Bank='BOI' ;

           

          load * Inline [

          Date ,           account id,  Bank,   Amount

          01/02/2016,  114AE ,          SBI ,        400

          01/02/2016,  114AE,          BOI,         -400

          ];

           

          Capture.PNG

            • Re: Two rows into Single Row
              scotly victor

              Date            account id  Bank   Amount

              01/02/2016  114AE           SBI         400

              01/02/2016  114AE          BOI         -400

              02/03/2016 122DG          Axis        700

              02/03/2016 122DG         Canara   -700

               

              Desired Output:

               

              Date            account id    Source Name  Recipent Name  source Amount recipent Amount

              01/02/2016  114AE           BOI                SBI                       -400               400

              02/03/2016 122DG           Canara             Axis                       -700             700

                • Re: Two rows into Single Row
                  Vineeth Pujari

                  as below

                   

                  LOAD Date,accountid,Bank as  ReceivingBank,Amount as ReceiptAmount

                  where Amount>0;

                  load * Inline [

                  Date ,accountid,Bank,Amount

                  01/02/2016,114AE,SBI ,400

                  01/02/2016,114AE,BOI,-400

                  02/03/2016,122DG,Axis,700

                  02/03/2016,122DG,Canara,-700

                  ];

                   

                   

                  left join

                   

                   

                  LOAD Date,accountid,Bank as SourceBank,Amount as SourceAmount

                  where Amount*1<0;

                  load * Inline [

                  Date ,accountid,Bank,Amount

                  01/02/2016,114AE,SBI ,400

                  01/02/2016,114AE,BOI,-400

                  02/03/2016,122DG,Axis,700

                  02/03/2016,122DG,Canara,-700

                  ];

                  • Re: Two rows into Single Row
                    omar bensalem

                    In that case:

                     

                    table:

                    load Date,"account id",Amount as  RecipientAmount, Bank as Recipient

                    where Amount>0;

                    load * Inline [

                    Date ,           account id,  Bank,   Amount

                    01/02/2016,  114AE ,          SBI ,        400

                    01/02/2016,  114AE,          BOI,         -400

                    02/03/2016, 122DG  ,        Axis ,       700

                    02/03/2016, 122DG  ,       Canara  , -700

                    ];


                    left join(table)


                    load Date,"account id",Amount as  SourceAmount, Bank as Source

                    where Amount<0;

                    load * Inline [

                    Date ,           account id,  Bank,   Amount

                    01/02/2016,  114AE ,          SBI ,        400

                    01/02/2016,  114AE,          BOI,         -400

                    02/03/2016, 122DG  ,        Axis ,       700

                    02/03/2016, 122DG  ,       Canara  , -700

                    ];

                    result:

                    Capture.PNG

                • Re: Two rows into Single Row
                  Anil Babu Samineni

                  Or May be front-end solution

                  Capture.PNG

                  • Re: Two rows into Single Row
                    Antonio Mancini

                    Hi,

                    QlikView Script

                    Temp:
                    LOAD *,Autonumber(RowNo(),Date) as Count;
                    LOAD * Inline [
                    Date,account id,Bank,Amount
                    01/02/2016,114AE,SBI,400
                    01/02/2016,114AE,BOI,-400
                    02/03/2016,122DG,Axis,700
                    02/03/2016,122DG,Canara,-700
                    ]
                    ;
                    Table:
                    LOAD
                    Date,[account id],Bank as [Source Name],Amount as [source Amount]
                    Resident Temp
                    Where Count = 2;
                    Left Join (Table)
                    LOAD
                    Date,[account id],Bank as [Recipient Name],Amount as [Recipient Amount]
                    Resident Temp
                    Where Count
                    = 1;
                    Drop Table Temp;

                    Regards,

                    Antonio