Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Sokkorn
Honored Contributor

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
Siva_Sankar
Honored Contributor

Re: credit debit

See the attached qv file. Hope it helps.

Partner
Partner

Re: credit debit

hi,

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

Sokkorn
Honored Contributor

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

Partner
Partner

Re: credit debit

Thanks lot it is working

Sokkorn Cheav

Sokkorn
Honored Contributor

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

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 ?

Sokkorn
Honored Contributor

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

Partner
Partner

Re: credit debit

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

Sokkorn
Honored Contributor

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