Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an excel file source .. Few of the columns as below , One of the Fields -Code has multiple values within it and
CertificateNumber | Amount | Code
10099876 |100.52| 1-H2;2-L2
10099887 |83.92| 1-K2;2-GC;3-RA
10099765 |98.23| 1-9;2-8E
Output Required: The Split needs to be done.
CertificateNumber | Amount | Code
10099876 |100.52| H2
10099876 |100.52| L2
10099887 |83.92| K2
10099887 |83.92| GC
10099887 |83.92| RA
10099765 |98.23| 9
10099765 |98.23| 8E
Thanks for the suggestions
Hi Toufiq,
Perfect ..I tried it works very well. I have a last scenario to align ..(here there are no zeros)
My percentage below if you see is not equal to 100 Percent. So I would forcefully make this or distribute like this
so i need to go one by one
10099876 |100.52| 1-H2;2-L2| 1-20;2-40 = 20 +40 = 60
20 * 100/60 = 33.333 and 40* 100/60 = 66..67 which would be equal to 100
similiarly for the second one
10099887 |83.92| 1-K2;2-GC;3-RA | 1-30;2-20;3-2.5 = 52.5 ..
30*100/52.5 and 20*100/52.5 and 2.5 *100/52.5
CertificateNumber | Amount | Code | Percentage
10099876 |100.52| 1-H2;2-L2| 1-20;2-40
10099887 |83.92| 1-K2;2-GC;3-RA | 1-30;2-20;3-2.5
10099765 |98.23| 1-9;2-8E | 1-22;2-30;3-10;4-9
is it possible to have this calculation along with the previous version of code you shared
Thanks a ton for your help.
if there is no 0, does the sum of the values always have to be 100?
Correct. The Total allocation percentage should be equal to 100 % .So the source file has sometimes the below scenarios..
1. 1-20;2-30;3-50 - If we just split it works correctly - total to 100%
2. 1-20;2-30;3-0 - Your code was working for this - 3 gets 50%
3.1-0;2-0;3-0 - Your code again was working for this - 3 gets 33.333%
4.1-25;2-20;3-20 - Last scenario, we have to go one by one then raise the percentage for each to make it 100..so it gets equally distributed..
Check this solution.
tab1:
LOAD *,AutoNumber(RowNo(),RecID) As RowID, SubField(SubField(Percentage,';'),'-',2) As WP;
LOAD RecNo() As RecID,* INLINE [
Percentage
1-20;2-30;3-50
1-20;2-30;3-0
1-0;2-0;3-0
1-25;2-20;3-20
1-35;2-15
];
Left Join(tab1)
LOAD RecID, Count(RowID) As C1, Count(If(WP>0,WP)) As CNZ, Count(If(WP=0,WP)) As CZ
, Sum(WP) As S1, 100-Sum(WP) As Bal
Resident tab1
Group By RecID;
tab2:
NoConcatenate
LOAD *
Resident tab1
Where Bal=0;
Concatenate(tab2)
LOAD RecID, RowID, Percentage, If(WP<>0,WP,P1) As WP, C1, CNZ, CZ, S1, Bal, P1
;
LOAD *, Num(Bal/CZ,'0.##') As P1
Resident tab1
Where Bal>0 And CZ>0;
Concatenate(tab2)
LOAD *
Resident tab1
Where Bal>0 And CZ=0
;
Concatenate(tab2)
LOAD RecID, C1+1 As RowID, Percentage, Bal As WP, C1, CNZ, CZ, S1, Bal
Resident tab1
Where Bal>0 And CZ=0 And RowID=1
;
Drop Table tab1;
Output
OK,
Maye be this version.
Data:
load CertificateNumber,Amount,Code,OldCode,Percentage as OldPercentage,subfield(subfield(Percentage,';'),'-',2) as Percentage;
load CertificateNumber,Amount,Code as OldCode,subfield(subfield(Code,';'),'-',2) as Code,Percentage;
load * inline [
CertificateNumber | Amount | Code | Percentage
10099876 |100.52| 1-H2;2-L2| 1-10;2-20
10099887 |83.92| 1-K2;2-GC;3-RA | 1-11;2-10;3-25
10099765 |98.23| 1-9;2-8E | 1-21;2-19
10099876 |100.52| 1-H2;2-L2| 1-0;2-0
10099887 |83.92| 1-K2;2-GC;3-RA | 1-50;2-0;3-0
9999999 |98.23| 1-9;2-8E;9-9 | 1-25;2-20;3-20
](delimiter is '|');
tmp:
noconcatenate
load CertificateNumber,count(CertificateNumber) as CountPourcentage resident Data where Percentage=0 group by CertificateNumber,OldCode;
right join load * resident Data;
drop table Data;
left join(tmp)
load sum(Percentage) as SumPourcentage,if(min(Percentage),1,0) as Flag, CertificateNumber,OldCode,OldPercentage resident tmp group by CertificateNumber,OldCode,OldPercentage;
output:
noconcatenate
load *,
if(Flag=1 and SumPourcentage<>100,num((Percentage*100/SumPourcentage),'###0'),if(Percentage=0,num((100-SumPourcentage)/CountPourcentage,'###0'),Percentage)) as NewPercentage
resident tmp;
drop table tmp;
drop fields SumPourcentage,CountPourcentage,OldCode,OldPercentage,Flag;
output :
1-25;2-20;3-20 you can see that is now 31 35 31
More readable.
tab1:
LOAD *,AutoNumber(RowNo(),RecID) As RowID, SubField(SubField(Percentage,';'),'-',2) As WP;
LOAD RecNo() As RecID,* INLINE [
Percentage
1-20;2-30;3-50
1-20;2-30;3-0
1-0;2-0;3-0
1-25;2-20;3-20
1-35;2-15
];
Left Join(tab1)
LOAD RecID, Count(RowID) As C1, Count(If(WP>0,WP)) As CNZ, Count(If(WP=0,WP)) As CZ
, Sum(WP) As S1, 100-Sum(WP) As Bal
Resident tab1
Group By RecID;
tab2:
NoConcatenate
LOAD *
Resident tab1
Where Bal=0;
Concatenate(tab2)
LOAD RecID, RowID, Percentage, If(WP<>0,WP,P1) As WP, C1, CNZ, CZ, S1, Bal, P1
;
LOAD *, Num(Bal/CZ,'0.##') As P1
Resident tab1
Where Bal>0 And CZ>0;
Concatenate(tab2)
LOAD *
Resident tab1
Where Bal>0 And CZ=0
;
Concatenate(tab2)
LOAD RecID, C1+1 As RowID, Percentage, Bal As WP, C1, CNZ, CZ, S1, Bal
Resident tab1
Where Bal>0 And CZ=0 And RowID=1
;
tab3:
LOAD RecID, Percentage As OldPercentage
Resident tab2;
Left Join(tab3)
LOAD RecID, Concat(RowID&'-'&WP,';') As NewPercentage
Resident tab2
Group By RecID;
Drop Table tab1, tab2;
Output .
Thanks Toufiq, I Will test on my box and let you know how it is working...
Thanks Saran. Iam almost reaching the solution with toufiq answer- still yet to test. But i checked your output, if you see..
1-25;2-20;3-20 --> Your output -- is 1-25;2-20;3-20 ;4-35 -so normally it has to equally distribute within 1,2 and 3 -- 4 should not be added.