Hi Experts,
Need your support
I want to group/concatenate column 1,2,12 and 13 and if found duplicate then need to sum value of column 17,20,21 and column22. After concatenation if found unique then no action to be taken.
Secondly want to make duplicate rows basis on grouping/concatenation of column 1,2,12 and 13 as single rows in outputfile and those single rows should have column sum (of column 17,20,21 and column22 from input file)
Input file :
Supp|ReturnPeriod|Month|OrgPOS|OrgHSNorSAC|OrgUnitOfMeasurement|OrgQuantity|OrgRate|OrgTaxableValue|OrgeComGSTIN|OrgeComSupplyValue|NewPOS|NewHSNorSAC|NewUnitOfMeasurement|NewQuantity|NewRate|NewTaxableValue|NeweComGSTIN|NeweComSupplyValue|IntegratedTaxAmount|CentralTaxAmount|StateUTTaxAmount|CessAmount
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|0|||||0||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|500||||45|45||
27AAACH1458C1ZZ|032018||||||||||27|9984|||1|7794||||701.46|701.46||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|500||||45|45||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|1000||||90|90||
27AAACH1458C1ZZ|102017||||||||||27|9984|||1|499.12||||44.92|44.92||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|500||||45|45||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|500||||45|45||
37AAACT2438A1ZS|042018||||||||||37|9984|||1|300||||27|27||
37AAACT2438A1ZS|042018||||||||||37|9984|||1|300||||27|27||
24AAACT2438A1ZZ|042018||||||||||24|9984|||1|500||||45|45||
36AAACT2438A1ZU|042018||||||||||36|9984|||1|300||||27|27||
33AAACT2438A1Z0|042018||||||||||33|9984|||1|500||||45|45||
24AAACT2438A1ZZ|042018||||||||||24|9984|||1|500||||45|45||
09AAACT2438A1ZR|042018||||||||||09|9984|||1|1000||||90|90||
37AAACT2438A1ZS|042018||||||||||37|9984|||1|300||||27|27||
37AAACT2438A1ZS|042018||||||||||37|9984|||1|300||||27|27||
24AAACT2438A1ZZ|042018||||||||||24|9984|||1|500||||45|45||
24AAACT2438A1ZZ|042018||||||||||24|9984|||1|500||||45|45||
36AAACT2438A1ZU|042018||||||||||36|9984|||1|300||||27|27||
32AAACT2438A1Z2|042018||||||||||32|9984|||1|500||||45|45||
09AAACT2438A1ZR|042018||||||||||09|9984|||1|1000||||90|90||
36AAACT2438A1ZU|042018||||||||||36|9984|||1|300||||27|27||
32AAACT2438A1Z2|042018||||||||||32|9984|||1|500||||45|45||
Sample Expected output
Supp|ReturnPeriod|Month|OrgPOS|OrgHSNorSAC|OrgUnitOfMeasurement|OrgQuantity|OrgRate|OrgTaxableValue|OrgeComGSTIN|OrgeComSupplyValue|NewPOS|NewHSNorSAC|NewUnitOfMeasurement|NewQuantity|NewRate|NewTaxableValue|NeweComGSTIN|NeweComSupplyValue|IntegratedTaxAmount|CentralTaxAmount|StateUTTaxAmount|CessAmount
37AAACT2438A1ZS|042018||||||||||37|9984|||1|600||||54|54||
36AAACT2438A1ZU|042018||||||||||36|9984|||1|600||||54|54||
09AAACT2438A1ZR|042018||||||||||09|9984|||1|2000||||180|180||
Code : i have tried
awk -F " " '
NR==1 {print; next}
NF {e[$1$2$12$13]++ ; a[$1$2$12$13]+=$17; b[$1$2$12$13]+=$20; c[$1$2$12$13]+=$21; d[$1$2$12$13]+=$22;}
END {for(i in a)print i}
' input.txt
Code output : Not correct because is column1 value "27AAACH1458C1ZZ|042018" is repeating twice and also sum of these rows are not correct. Same problem is with others column 1 values like "09AAACT2438A1ZR"
Supp|ReturnPeriod|Month|OrgPOS|OrgHSNorSAC|OrgUnitOfMeasurement|OrgQuantity|OrgRate|OrgTaxableValue|OrgeComGSTIN|OrgeComSupplyValue|NewPOS|NewHSNorSAC|NewUnitOfMeasurement|NewQuantity|NewRate|NewTaxableValue|NeweComGSTIN|NeweComSupplyValue|IntegratedTaxAmount|CentralTaxAmount|StateUTTaxAmount|CessAmount
09AAACT2438A1ZR|042018||||||||||09|9984|||1|1000||||90|90||
24AAACT2438A1ZZ|042018||||||||||24|9984|||1|500||||45|45||
37AAACT2438A1ZS|042018||||||||||37|9984|||1|300||||27|27||
27AAACH1458C1ZZ|102017||||||||||27|9984|||1|499.12||||44.92|44.92||
33AAACT2438A1Z0|042018||||||||||33|9984|||1|500||||45|45||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|1000||||90|90||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|500||||45|45||
36AAACT2438A1ZU|042018||||||||||36|9984|||1|300||||27|27||
27AAACH1458C1ZZ|032018||||||||||27|9984|||1|7794||||701.46|701.46||
32AAACT2438A1Z2|042018||||||||||32|9984|||1|500||||45|45||
27AAACH1458C1ZZ|042018||||||||||27|9984|||1|0|||||0||