Please help

Wed, 11 Sep 2013 11:48:50 GMT

Hi all,

please help me with a problem.

i need to have an script that will calculate FIFO the balance of a card. The card can have multiple transaction, each transaction will add or remove an certain amount of money from the card, and i need to mach the transaction id's depending if the removing transaction is consuming the entire add transaction or not. Here is an example:

The initial table:

TransactionDate	CardID	TranIdADD	TranIdSub	TranAmount
01/01/2013	1	1		100
02/01/2013	1	2		50
03/01/2013	1		3	-75
01/02/2013	1		4	-50
02/02/2013	2	5		100
02/01/2013	2		6	-80

and the result must be something like:

TransactionDate	CardID	TranIdADD	TranIdSub	TranAmount	CalculatedTranAmount
01/01/2013	1	1	4	100	0
02/01/2013	1	2		50	25
03/01/2013	1	1	3	-75	-75
01/02/2013	1	2	4	-50	-25
02/02/2013	2	5		100	20
02/01/2013	2	5	6	-80	20

To explain better:

for card with ID = 1

- transaction with ID = 3 remove from transaction with ID = 1 the amount and remains 25 (100-75=25) and the TranIdADD is updated with 1

- transaction with ID = 4 remove from transaction with ID = 1 all amount and remains 0 and also from transaction with ID = 2 the rest of 25. On the transaction with ID = 1 remains 0 and on the transaction with ID = 2 remains 25. For the transaction with Id = 4, the TranIdAdd is updated with 2 (because that transaction was covering the entire amount). For the transaction with the ID = 1, the TranIdSUB is updated with 4 (the transaction id that finished the entire amount). The transaction with ID = 2 remains with TranIdSub = null/nothing because it has amount, and the amount (25) represents the amount of the card

topic Please help in QlikView

Please help