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

[Taoufiq_Zarra]

@raadwiptec 

May be :

load CertificateNumber,Amount,subfield(subfield(Code,';'),'-',2) as Code;
load * inline [
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
](delimiter is '|');

output :

[raadwiptec]

Thanks Toufiq, This is working, But i missed one information to put in my orginal post, there was another field with the same pattern which needs to be split

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

Output Required: The Split needs to be done.

CertificateNumber | Amount | Code |Percentage

10099876 |100.52| H2|10

10099876 |100.52| L2|20

10099887 |83.92| K2|15

10099887 |83.92| GC|11

10099887 |83.92| RA|10

10099765 |98.23| 9|25|21

10099765 |98.23| 8E|19

[Taoufiq_Zarra]

@raadwiptec  May be :

Data:
load CertificateNumber,Amount,Code,subfield(subfield(Percentage,';'),'-',2) as Percentage;
load CertificateNumber,Amount,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
](delimiter is '|');

[Saravanan_Desingh]

How the K2 got 15 here? Also please clarify GC, RA and 9.

[raadwiptec]

Thanks Toufiq . Your solution is working perfectly, The initial idea was only to split, Iam not sure if you have some insights with one more solution which we we would like to add . There are cases when percentages are zeros and secondly the percentage should be finally equal to 100 percent

For Ex: the percentage tends to '0' which is not correct - In such cases we would like to get the output as 

if it is 1-0;2-0 then 1-50;2-50

If it is 1-0;2-0;3-0 then 1-33.33;2-33.33;3-33.33

if it is 1-0;2-0;3-0;4-0 then 1-25;2-25;3-25;4-25

if it is 1-25;2-0 then 1-25;2-75

the goal is to make this as 100%

if it is 1-0 then 1-100

this is exceptionally for cases when all are getting to 0

CertificateNumber | Amount | Code | Percentage

10099876 |100.52| 1-H2;2-L2| 1-0;2-0

10099887 |83.92| 1-K2;2-GC;3-RA | 1-0;2-0;3-0

10099765 |98.23| 1-9;2-8E | 1-0;2-0;3-0;4-0

[Taoufiq_Zarra]

Maye be this solution :

Data:
load CertificateNumber,Amount,Code,subfield(subfield(Percentage,';'),'-',2) as Percentage;
load CertificateNumber,Amount,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-0;2-0;3-0

10099765 |98.23| 1-9;2-8E | 1-0;2-0;3-0;4-0
](delimiter is '|');
left join

load Code,CertificateNumber,count(CertificateNumber) as counttmp resident Data group by CertificateNumber,Code;

output:
noconcatenate

load *,if(Percentage=0,num(100/counttmp,'###0'),Percentage) as NewPercentage resident Data;

drop table Data;
drop fields counttmp;

output :

[raadwiptec]

Hi Toufiq. Thanks iam testing your code.

Resharing the last part again. So when their is  a case -- similiar to the 4th line with 

--> if it is 1-25;2-0 then 1-25;2-75

for ex: one has already value 1-25 and 2- 0 where the 2 should be assigned as 75 ------->  2-75

----------------------------------------------------

if it is 1-0;2-0 then 1-50;2-50

If it is 1-0;2-0;3-0 then 1-33.33;2-33.33;3-33.33

if it is 1-0;2-0;3-0;4-0 then 1-25;2-25;3-25;4-25

if it is 1-25;2-0 then 1-25;2-75

the goal is to make this as 100%

if it is 1-0 then 1-100

[Taoufiq_Zarra]

New 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

10099765 |98.23| 1-9;2-8E | 1-0;2-0;3-0;4-0
](delimiter is '|');

tmp:
noconcatenate

load CertificateNumber,OldCode, count(CertificateNumber) as CountPourcentage resident Data where Percentage=0 group by CertificateNumber,OldCode;
left join load * resident Data;

drop table Data;

left join(tmp)

load sum(Percentage) as SumPourcentage, CertificateNumber,OldCode,OldPercentage resident tmp  group by CertificateNumber,OldCode,OldPercentage;


output:
noconcatenate

load *,if(Percentage=0,num((100-SumPourcentage)/CountPourcentage,'###0'),Percentage) as NewPercentage resident tmp;

drop table tmp;
drop fields SumPourcentage,CountPourcentage,OldCode,OldPercentage;

output :

for this example :

10099887 |83.92| 1-K2;2-GC;3-RA | 1-50;2-0;3-0

NewPercentage is 50 25 25

[raadwiptec]

thanks toufiq.. I will transform the code into my box and come back to you..