Split multiple values from with in a Column

[raadwiptec]

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

[raadwiptec]

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.

[Taoufiq_Zarra]

if there is no 0, does the sum of the values always have to be 100?

[raadwiptec]

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..

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

Output

[Taoufiq_Zarra · Accepted solution]

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

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

Output .

[raadwiptec]

Thanks Toufiq, I Will test on my box and let you know how it is working...

[raadwiptec]

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.