Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
];
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
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
];
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:
Or May be front-end solution
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