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

 

Code
SELECT 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

 

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