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
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
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 :
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
@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 '|');
How the K2 got 15 here? Also please clarify GC, RA and 9.
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
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 :
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
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
thanks toufiq.. I will transform the code into my box and come back to you..