Need a Logic for balance

[Former Member]

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

[Former Member]

Sorry the attached Doc is in XML, please ignore

Thanks

Madhu

[former_member186897]

Hi Madhu,

You may follow the below steps to get the result.

Step 1: Declare a global variable named $G_New_Bal of type int as shown in below diagram.

Step 2: Create a function named CF_CAL_BALANCE. Copy paste the below code in your function editor.

    if ($rank = 1)

         $G_New_Bal = $balance;

     else

          $G_New_Bal = $G_New_Bal - $paid_amt;

     return ($G_New_Bal);

Then Declare 3 input parameters  (all of type int) in the order as shown below. It would look like below.

Step 3: Validate the function and see there should not be any error. You will get one warning as you are using a global variable inside this function. Save this function.

Step 4: Now, Use this function while mapping your new field. Use below line of code.

CF_CAL_BALANCE(EMP_BALANCE.RANK,EMP_BALANCE.BALANCE,EMP_BALANCE.PAID_AMOUNT)

In query editor your code should look like below.

Step 5: Validate the Job and ensure there is no error. Then execute the Job. You should get your result as shown below.

Hope this helps. Do let us know when you successfully implement it at your end.

Regards,

MS Ansari

[Former Member]

Hi Mohd,

It worked Perfect for the new balance field, thank you so much, Also the balance field should change

In the above result set the 3rd Week balance should be same as 2nd week new balance

In other words, 2nd Week Balance should be same as first week new balance as shown below

Input

SSN	DATE	BALANCE	PAID_AMOUNT
111111111	1ST Week	1000	100
111111111	2nd Week	1000	100
111111111	3rd Week	1000	100
111111112	1ST Week	1000	100
111111112	2nd Week	1000	100
111111112	3rd Week	1000	100
111111112	4TH Week	1000	100

output

SSN	DATE	BALANCE	NEW_BALANCE
111111111	1ST Week	1000	1000
111111111	2nd Week	1000	900
111111111	3rd Week	1000	800
111111112	1ST Week	1000	1000
111111112	2nd Week	1000	900
111111112	3rd Week	1000	800
111111112	4TH Week	1000	700

Thanks for your Effort

[Former Member]

Sorry in the previous reply

the output should be as below

SSN	DATE	BALANCE	NEW_BALANCE
111111111	1ST Week	1000	1000
111111111	2nd Week	1000	900
111111111	3rd Week	900	800
111111112	1ST Week	1000	1000
111111112	2nd Week	1000	900
111111112	3rd Week	900	800
111111112	4TH Week	800	700

[former_member186897]

Ok, if it has been resolved then you may close this thread.

[Former Member]

Only resolved for one column, There is also a Balance field

the output should be as below

SSN	DATE	BALANCE	NEW_BALANCE
111111111	1ST Week	1000	1000
111111111	2nd Week	1000	900
111111111	3rd Week	900	800
111111112	1ST Week	1000	1000
111111112	2nd Week	1000	900
111111112	3rd Week	900	800
111111112	4TH Week	800	700

[Former Member]

i tried with below code for BALANCE field upto 3 levels it works fine after that not getting expected result

if ($RANK = 1)
    
      $G_BAL = $BALANCE;

else

    IF ($RANK = 2)

          $G_BAL = $BALANCE;

    else
    
       $G_BAL = $BALANCE - $PAID ;

return ($G_BAL);

Any Suggesion

Thanks

Madhu

[Former Member]

Hello,

Please try below steps. It does not use any custom function but it works even if you have 4th/5th week data.

Step 1: Apply sort on SSN and then Rank columns.

Step 2: New_Balance = balance - paid_amount * (rank - 1). your output will be

SSN          data            balance paid_amount rank new_balance

111111111    1st week date   1000     100         1      1000

111111111    2nd week date   1000     100         2      900

111111111    3rd week date   1000     100         3       800

111111122    1st week date   1000     100         1     1000

111111122    2nd week date   1000     100         2      900

111111122    3rd week date   1000     100         3      800

111111122    4th week date   1000     100         4      700

Step 3: Balance = ifthenelse(rank = 1, balance, previous_row_value(new_balance)). The output will now be:

SSN          data            balance paid_amount rank new_balance

111111111    1st week date    1000     100         1      1000

111111111    2nd week date   1000     100         2        900

111111111    3rd week date      900     100         3        800

111111122    1st week date    1000     100         1      1000

111111122    2nd week date   1000     100         2        900

111111122    3rd week date      900     100         3        800

111111122    4th week date      800     100         4        700

Regards,

Rajesh

[Former Member]

hi Rajesh,

I was on vacation for one week

i tried as per your above logic

for new_balance field upto 3 ranks i am getting correct but for rank 4 i am not getting as expected

i am using  balance - PAID_AMOUNT * (RANK -1)

for example

Input

balance  paid_amount rank ssn

1320        0                  1       111111111

1320        17                 2      111111111

1320        17                 3      111111111

1320         4                  4      111111111

Output

balance  paid_amount rank ssn                new_balance

1320        0                  1       111111111   1320

1320        17                 2      111111111   1303

1320        17                 3      111111111    1286

1320         4                  4      111111111    1308(should be 1284)

For the balance field, for rank 2 i am getting null

Using ifthenelse( RANK = 1, balance, previous_row_value(new_balance))

output getting as

balance  paid_amount rank ssn                new_balance

1320        0                  1       111111111   1320

null         17                 2      111111111   1303

1303        17                 3      111111111    1286

1286         4                  4      111111111    1308

any suggesion please

Thanks

Madhu

[Former Member]

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.

[Former Member]

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

Thanks

Madhu

[Former Member]

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.

[Former Member]

Hi,

I tried, it did not work, can you send me step by step, If possible please send it to my email id

reddmadhu@gmail.com

Thanks

Madhu

[former_member186897]

Madhu, Solution given is pretty straight forward. Let us know what exactly you have done at your end so that we can track where it is going wrong.

[Former Member]

I think this solution is clear and step by step one...as you tried this what are the errors you are getting f. If you can post those it will be helpful to find the solution.

[former_member187605]

Have you tried my solution? Even simpler. Input parameters only.

[Former Member]

Hi Dirk,

What about the balance, you returned values only for new_balance. Can we use Global Variables in a custom function?

[former_member187605]

Sure, we can.

[Former Member]

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

[former_member186897]

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

[Former Member]

Hi,

I need a Full logic to perform this,Please me full logic

Thanks

Madhu

[Former Member]

Hi,

I am new to BODS, I need step by step to perform this task,Please guide me

Thanks

Madhu

[former_member186897]

Add another column in your output and name it as new_balance.

Your code would look like below one.

decode

(

data='1st week date', balance,

data='2nd week date', balance-paid_amount,

data='3nd week date', balance-paid_amount,

balance

)

Is the data field is of a date datatype? If yes, then you need to identify in which weeks it falls and then apply this logic.

I am not sure about what is your default value if none of the above condition satisfies. You can change it as per your requirement.

Hope this helps.

[Former Member]

I have a table

SSN          date            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          date            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 tried decode taking rank for balance, but i am not getting,IF you can please help me out

I am using data service version 4.1

Need a Logic to perform above

[former_member187605]

The proposed solution won't work, because it does not aggregate.

You need a global variable and a custom function F with three parameters ($balance, $paid and $rank) to resolve this.

The custom function logic:

     if ($rank = 1)

         $GV = $balance;

     else

          $GV = $GV - $paid;

     return ($GV);

In the mapping for new_balance specify:

     F(balance, paid, rank)

[Former Member]

i created 3 parameters, it does not allow to create GV in the custom function

Any suggesion please

thanks

Madhu