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,

Showing Answers 1 - 10 of 10 Answers

shiva4mba Profile Answers by 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

sonu.80 Profile Answers by sonu.80

• Oct 27th, 2010

If you know the total contibution then

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

Mohammed Fakruddin Profile Answers by Mohammed Fakruddin

• Dec 24th, 2010

Use the concept of mapping parameter or mapping variable.

sudhir24 Profile Answers by 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 Profile Answers by 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

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

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

Mohan Krishna Bellamkonda

• Sep 7th, 2017

```CodeSELECT B.ID,(B.CONTRIBUTION/A.CONTRIB)*100||% AS EXPECTED VALUE FROM
(SELECT FAMILY_ID,SUM(CONTRIBUTION) FROM FAMILY
GROUP BY FAMILY_ID) A, FAMILY B
WHERE A.FAMILY_ID=B.FAMILY_ID```

Mohan Krishna Bellamkonda

• Sep 7th, 2017

`CodeSELECT EMPLOYEE_ID, (CONTRIBUTION / SUM (CONTRIBUTION) OVER (PARTITION BY FAMILY ID) ) * 100 || % AS EXPECTED_VALUE FROM FAMILY;`