Skip to Content

Need a Logic for balance

Hi Experts,

I have a table

SSN data balance paid_amount rank
111111111 1st week date 1000 100 1
111111111 2nd week date 1000 100 2
111111111 3rd week date 1000 100 3
111111122 1st week date 1000 100 1
111111122 2nd week date 1000 100 2
111111122 3rd week date 1000 100 3


I want the output as

SSN data balance New_balance
111111111 1st week date 1000 1000
111111111 2nd week date 1000 900
111111111 3rd week date 900 800
111111122 1st week date 1000 1000
111111122 2nd week date 1000 900
111111122 3rd week date 900 800

For first New_balance values will be same as balance
for second week balance = new_balance and new_balance = balance-paid_amount
for third week balance = new_balance and new_balance = balance-paid_amount

I am using data service version 4.1

Need a Logic to perform above

Thanks

Madhu

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Posted on Mar 31, 2014 at 08:18 PM

    Use decode function to perform these operations. you can apply the calculations as well.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 03, 2014 at 01:37 PM

    As per the given source and requirement I have a dataflow as shown here

    create a custom function as shown below

    Here few of the parameters must be input/output...that are shown in the below image. Define two parameters as input/output and then for new balance use function call.

    the output is generated as follows.


    home1.JPG (146.4 kB)
    home.JPG (176.5 kB)
    last.JPG (171.0 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • i tried as per your solution

      i am getting error as

      [Query:Query_1]
      Invalid mapping expression for column <Query_1.BALANCE>. Additional information: <Cannot parse expression.

      Passing an expression as an output parameter <$BALANCE> to <Function cf_scn1>. Only a variable can be specified as an output parameter.. (BODI-1112351)>. (BODI-1111081)

      Any suggesion please

      Thanks

      Madhu

  • Posted on Apr 06, 2014 at 03:56 AM

    Please find the attached step by step screen shots what i have tried along with the error

    Thanks

    Madhu


    balance.xml (690.4 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 06, 2014 at 03:57 AM

    Sorry the attached Doc is in XML, please ignore

    Thanks

    Madhu

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member uhdam reddy

      Hello Madhu,

      I think the logic has to be implemented in different way to resolve the issues listed.

      Step 1: Create new output field Rank_New = decode(RANK = 1, 1, 2). Here, we are keeping the records other than rank 1 to new group

      Step 2: Apply sort or SSN, Rank_New and then Rank

      Step 3: Output of Step 2 is connected to 2 query transforms. No need to apply any filter.

      Step 4: Join previous 2 query transforms (Inner join). Use below join criteria. Get SSN, Rank, Balance from Query_Current. Get Paid Amt from Query_Old

      Query_Old.SSN = Query_Current.SSN AND

      Query_Current.RANK_NEW= Query_Old.RANK_NEW AND

      Query_Old.RANK <= Query_Current.RANK

      Step 5: Aggregate Paid Amount. Sum(Paid_amt) and all other fields in group by clause

      Step 6: New_balance = decode(rank = 1, balance, balance - paid_amt)

      Step 7: Create new field for calculating balance, balance_inter = previous_row_value(New_balance)

      Step 8: Balance = decode(rank = 1, balance, balance_inter)

      Hope the above steps will resolve your issue. If you are getting incorrect values in new balance and balance, apply sort on ssn and rank at the step 5.

      Balance.PNG (15.7 kB)

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.