Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
scotly-victor
Not applicable

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
Not applicable

Re: Two rows into Single Row

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
Not applicable

Re: 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

vinieme12
Not applicable

Re: Two rows into Single Row

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
Not applicable

Re: Two rows into Single Row

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

loveisfail
Not applicable

Re: Two rows into Single Row

Or May be front-end solution

Capture.PNG

Life is so rich, and we need to respect to the life !!!
antoniotiman
Not applicable

Re: Two rows into Single Row

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