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?

[OmarBenSalem]

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

];

[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

[vinieme12]

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

];

[OmarBenSalem]

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:

[Anil_Babu_Samineni]

Or May be front-end solution

[antoniotiman]

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