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

[Qlik__Vinodh]

hi,

this is my sample data....

[Sokkorn]

Let try this

[Data]:
LOAD *,
    RangeSum(bal + [credit] - [debit], Peek('bal_amt'))    AS bal_amt;
LOAD accountno, 
     transno, 
    IF(ISNULL(debit) OR LEN(debit)<0,0,debit)    AS debit, 
     //credit, 
     IF(ISNULL(credit) OR LEN(credit)<0,0,credit)    AS credit, 
    // bal,
     IF(ISNULL(bal) OR LEN(bal)<0,0,bal)    AS bal
FROM Book1.xlsx
(ooxml, embedded labels, table is Sheet1);

See sample attached file.

Regards,

Sokkorn

[Qlik__Vinodh]

thanks lot Sokkorn,it is working ..

[Sokkorn]

Hi Vinodh,

Your are the most welcome friend.

Cheer!!

Sokkorn

[Qlik__Vinodh]

hi Sokkorn

it is working for only one account-no,i need to work different account-no also ......

[Sokkorn]

Hi,

Let check in the attached file.

Do let me know, if you need further assistant.

Regards,

Sokkorn

[Qlik__Vinodh]

thank you

[Sokkorn]

It is working for you?

[Qlik__Vinodh]

yes.it is working super..............

[Sokkorn]

Good friend. Just let you know that do not try with load script. We should use expression for a better one.