Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik__Vinodh
Partner - Creator II
Partner - Creator II

credit debit

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

1 Solution

Accepted Solutions
Sokkorn
Master
Master

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

View solution in original post

20 Replies
Siva_Sankar
Master II
Master II

See the attached qv file. Hope it helps.

Qlik__Vinodh
Partner - Creator II
Partner - Creator II
Author

hi,

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

Sokkorn
Master
Master

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
Partner - Creator II
Partner - Creator II
Author

Thanks lot it is working

Sokkorn Cheav

Sokkorn
Master
Master

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
Partner - Creator II
Partner - Creator II
Author

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

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

Sokkorn
Master
Master

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
Partner - Creator II
Partner - Creator II
Author

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

Sokkorn
Master
Master

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