Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
scotly-victor
Creator II
Creator II

Two rows into Single Row

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?

6 Replies
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

];

Capture.PNG

scotly-victor
Creator II
Creator II
Author

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
Champion III
Champion III

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

];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
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:

Capture.PNG

Anil_Babu_Samineni

Or May be front-end solution

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
antoniotiman
Master III
Master III

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