on 03-31-2014 7:20 PM
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
Sorry the attached Doc is in XML, please ignore
Thanks
Madhu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
I tried, it did not work, can you send me step by step, If possible please send it to my email id
Thanks
Madhu
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
Use decode function to perform these operations. you can apply the calculations as well.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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)
User | Count |
---|---|
88 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.