informaticainformatica-powercenter

Create the Mapping and Display Runs For Each Over and I Also Add First Over Runs in the Sum of Second Over Runs and Same for Third Over

Source table Cricket_Score:

Overs Balls Runs
1 1 1
1 2 2
1 3 4
1 4 0
1 5 1
1 6 2
2 1 3
2 2 1
2 3 1
2 4 4
2 5 6
2 6 0
3 1 2
3 2 1
3 3 1
3 4 6
3 5 0
3 6 4

I Want to an output like this:

Overs Total_Runs
1 10
2 25
3 39

Description: - For First Over means First 6 Balls I Want Sum of First 6 Balls that is 10. and For Second 6 Balls I Want Sum of First 6 Balls [Over] + Second 6 Balls That is 25 [10 + 15 = 25]. and For Third 6 Balls I Want Sum of First 6 Balls [Over] + Second 6 Balls + Third ^ Balls That is 39 [10 + 15 + 14 = 39].

Note: - 6 balls means one over.

How to create a mapping in for this scenario in Informatica / which logic should I use?

Solution

  • i will assume your data is EXACTLY like you have shown in your question. If its not like this in source then it will be a major issue. If its a table where data is not sorted, it will be an issue.

    Solution -

    1. Create an expression transformation with below ports - in below order. in - input port, v_variable port, out_* output port
    in_balls
in_runs  
in_overs
v_cumulative_runs= in_runs+ iif(isnull(v_cumulative_run),0,v_cumulative_run)
out_total_runs=v_cumulative_runs
out_overs=in_overs
    1. Use an aggregator -
    in_total_runs
in_out_overs -- group by this port
out_total_runs = max(in_total_runs)
    1. Attach in_out_overs and out_total_runs links to target.