credit debit

[Qlik__Vinodh]

hi,

i have bank transaction table like this

accno transactionid      date           balance      credit-amt      debit-amt

------------------------------------------------------------------------------------------------------

100     1                     11/5/12          5000          -                      -

100     2                      15/5/12            -            400                    -

100     3                    23/5/12             -               -                    200

100     4                     24/5/12            -               -                    500

this is the table .what i need the out table(it means qlikview staight table) is like this

accno transactionid      date           balance      credit-amt      debit-amt     bal_amt

--------------------------------------------------------------------------------------------------------

100     1                     11/5/12          5000          -                      -               5000

100     2                      15/5/12            -            400                   -               5400

100     3                    23/5/12             -               -                    200            5200

100     4                     24/5/12            -               -                    500             4700

please help me as soon as possible.........

Thanks

vinodh c

[Siva_Sankar]

See the attached qv file. Hope it helps.

[Qlik__Vinodh]

hi,

i need the sample application as i mention above ...

[Sokkorn]

Hi Vinodh,

Let try this script:

[Data]:
LOAD *,
    RangeSum(balance + [credit] - [debit], Peek('bal_amt'))    AS bal_amt;
LOAD * INLINE [
accno    ,     transactionid    ,    date    ,    balance    ,    credit    ,    debit
100    ,    1    ,    11-05-2012    ,    5000    ,        0,    0
100    ,    2    ,    15-05-2012    ,        0,    400    ,    0
100    ,    3    ,    23-05-2012    ,        0,        0,    200
100    ,    4    ,    24-05-2012    ,        0,        0,    500];

Or by expression:

RangeSum(Above(SUM(balance) + SUM(credit) - SUM(debit), 0, RowNo()))

Hope this help.

Regards,

Sokkorn

[Qlik__Vinodh]

Thanks lot it is working

Sokkorn Cheav

[Sokkorn · Accepted solution]

Hi Vinodh,

Your are welcome. Another one please help to mark as correct answer. So that other guy can find it easy for a solution. On the other hand, if you mark it correct meant that this case is close.

Regards,

Sokkorn

[Qlik__Vinodh]

i  have on dought on that ,it is u are making '0' insted of  '-'

if it is '-' it will working or not ?

[Sokkorn]

Hi Vinodh,

It is your data source store '-' instead of '0'? If like this, it will not work. So to get this working, we need to replace '-' to '0' value.

Regards,

Sokkorn

[Qlik__Vinodh]

ok,my data having '-' this how can i change this do u have any idea for that...

[Sokkorn]

Try this:

[Data]:

LOAD

     IF(ISNULL(CR) OR LEN(CR)<0,0,CR)     AS [Credit],

     .....

FROM .....

If can, share your sample data here. So that we can find a good solution together .

Regards,

Sokkorn