Scenario based

If I have a source as below:
Employeed, FamilyId, Contribution
1,A,10000
2,A,20000
3,A,10000
4,B,20
5,B,20
________________________________
And my desired target is as below:
EmployeeId,Contribution(In Percent)
1,25%
2,50%
3,25%
4,50%
5,50%
____________________________________________________________________________
Explanation: The contribution field in target is the individual Employee's share in a family's contribution.Say if total family contribution is 40000 then if A has contributed 10000 then target should have a value of 25%.
____________________________________________________________________________
Can you please suggest me an approach to solve the specified problem?

Thanks,

Questions by sohamasmi

Showing Answers 1 - 10 of 10 Answers

shiva4mba

  • Oct 20th, 2010
 

Here goes the sql override

SELECT A.empid(B.contribution/A.BB)*100 AS CONTRIBUT FROM  (SELECT empid,SUM(contribution) OVER (PARTITION BY familyid) AS BB FROM table1) A, (SELECT empid,contribution FROM table1) B WHERE A.empid=B.Empid

  Was this answer useful?  Yes

sonu.80

  • Oct 27th, 2010
 

If you know the total contibution then

select emplid ,concat((contribution*100)/40000,'%' contribution from target1

  Was this answer useful?  Yes

sudhir24

  • Dec 31st, 2010
 

Here is the solution if you do not want to use SQL override.

1. In the mapping, import Source twice. After first Src Qualifier, sum the contribution using Aggregator transformation, group by FamilyId.

2. Pass Aggregator output and second source qualifier output to Joiner, use join condition FAMILYID1 = FAMILYID.


3. Pass Joiner output to Expression and calculate the percentage using contribution from second SQ and sum of contribution * 100.

[Convert the value to char and concatenate '%', if exact output string is needed eg. '25%' instead of 25]


4. Pass Expression trans output to Sorter and sort by EMPLOYEEID. Pass this to Target.


Nagaraju Puppala

  • Feb 9th, 2011
 

You can writhe the source qualifier Query like this:


select s2.empno, (s2.sal/s1.sum_sal)*100 contribution
from
(select deptno, sum(sal) sum_sal from emp group by deptno) s1,
(select empno, deptno, sal from emp) s2
where s1.deptno=s2.deptno;


Cheers :)

Nagaraju Puppala

MAHESH MANAM

  • Aug 16th, 2012
 

Source --> sq --> 2 exps

In one expression take a variable port v_port store cum(Contribution) and check the FamilyID to previous ID

i_familyID
v_family=iff(i_familyID=v_priv_familyID),contribution+v_family,contribution)
v_priv_familyID=i_familyID
o_family=v_family

And pass this expression to aggrigator and in that based on FamilyID

So it retrieve the last row of that
i.e id,sum(Contribution)

Second Expression and the above aggrigator join based on FamilyID

After that pass the following to target

EmployeeID
Contribution(In Percent)=Contribution * o_family / 100

  Was this answer useful?  Yes

sachin

  • Dec 17th, 2012
 

you need a one source qualifier pipeline to aggregator then ==> a joiner to join aggregator and source qualifier ==> then expression to calculate percentage==> push it to target

  Was this answer useful?  Yes

saimurali

  • Oct 18th, 2013
 

Hi,

Source -> aggregator(sorted input option)-> expression t/r-> target

in aggregator t/r calculate the total sum of family contribution and connect all port to expression t/r, in this t/r contribution/toal family_contribution *100 and connect to target, u will get above required output

  Was this answer useful?  Yes

Mohan Krishna Bellamkonda

  • Sep 7th, 2017
 

Code
  1.  

  Was this answer useful?  Yes

Mohan Krishna Bellamkonda

  • Sep 7th, 2017
 

Code
  1.  

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions