Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Master
Master

Re: credit debit

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
Highlighted
Master II
Master II

Re: credit debit

See the attached qv file. Hope it helps.

Highlighted
Partner
Partner

Re: credit debit

hi,

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

Highlighted
Master
Master

Re: credit debit

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

Highlighted
Partner
Partner

Re: credit debit

Thanks lot it is working

Sokkorn Cheav

Highlighted
Master
Master

Re: credit debit

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

Highlighted
Partner
Partner

Re: credit debit

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

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

Highlighted
Master
Master

Re: credit debit

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

Highlighted
Partner
Partner

Re: credit debit

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

Highlighted
Master
Master

Re: credit debit

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