awk script concatenate two column and perform mutiplication

as7951 · April 3, 2018, 3:07am

Need your help in solving this puzzle. Any kind of help will be appreciated and link for any documents to read and learn and to deal with such scenarios would be helpful

Concatenate column1 and column2 of file 1. Then check for the concatenated value in Column1 of File2. If found extract the corresponding value of column2 and column3 of File2, Again concatenate column1 and column2 of File2. Now look for this concatenated value in File1 and if found

For example - concatenate column1(262881626) and column2(10) of File1. Then look for this concatenated(26288162610) value in column1 of File2 and extract corresponding column2 and column3 value of File2. Now again concatenate column1 and column2 of File2 and look for this concatenated(2628816261050) value in File1 and multiply exchange rate(2) fetched by concatenated value(26288162610) with taxable value(65) which corresponding to 2628816261050 of File1. Store the result of multiplcation value in column4(AD) of File1 only

I tried below code..but its not working.

awk -F"|" 'FNR==NR{a[$1$2]=$NF;next} {print $0,$1 in a?"|" a[$1]*$NF:""}' OFS="" File2 File1

File1

 Bill Doc     LineNo     Taxablevalue     AD
 262881626         10        245
 262881627         10        32
 262881628         20        456
 262881629         30         0
 262881630         40         45
2628816261050      11        65
2628816271060      12        34
2628816282070      13        45
2628816293080      14        0
2628816304090      15

File2

Bill.Doc     Item     Exch.Rate     
26288162610     50     2    
26288162710     60     1    
26288162820     70     45
26288162930     80     1    
26288163040     90     5

Output File

Bill Doc        LineNo  Taxablevalue   AD
262881626        10       245
262881627        10       32
262881628        20       456
262881629        30       0
262881630        40            
2628816261050     11      65            130
2628816271060     12      34            34
2628816282070     13      45            180
2628816293080     14      0              0
2628816304090     15

RudiC · April 3, 2018, 4:27am

In what way "its not working"? Usually, contemplating / scrutinizing misbehaviour and error messages will lead you to the root cause yourself, and the knowledge of either would help others help you as well.

as7951 · April 3, 2018, 4:43am

Hi Rudic,
it is not giving any error, but also not producing the desired output and not doing multiplication of column3 of File1 with column3 of file2 and not storing the multiplication result in neither column3 nor column4 of File1..
Pleas help rudic to achieve my output

RudiC · April 3, 2018, 4:57am

Well, you define the field separator to be | , but I don't see ANY | in your sample data. Did you consider dropping the -F"|" ?

Try as well

awk 'NR==FNR {EXR[$1 $2] = $3; next} $1 in EXR {$4 = $3 * EXR[$1]} 1' OFS="\t" file2 file1

The third exchange rate seems a bit high and thus leads to a rather high AD value...

as7951 · April 3, 2018, 5:22am

Hi Rudic,
Apologies, but actually to have more clarity for sample data i pasted sample file1 and file2 without pipe..though in actual file contains pipe..
for now code point of view you can cnsider space as well

RudiC · April 3, 2018, 5:27am

Hmmm - it doesn't really help if you post code and sample data that don't match.
Howsoever, did you solve your problem or does it persist?

as7951 · April 3, 2018, 6:00am

Hi Rudic,

Still have problem. getting 0 in column for all rows in which i am storing multiplication result.

---------- Post updated at 05:00 AM ---------- Previous update was at 04:57 AM ----------

Hi Rudic,
can you please explain the code as what variable is pointing towards which file

awk 'NR==FNR {EXR[$1 $2] = $3; next} $1 in EXR {$4 = $3 * EXR[$1]} 1' OFS="\t" file2 file1

RudiC · April 3, 2018, 7:01am

It uses the same method as your code in post#1 did: NR == FNR is TRUE for the first file parameter and FALSE for any further input files.
Without you showing code, data, and results - be they correct or not - I'm afraid we're poking in the dark.

as7951 · April 3, 2018, 2:22pm

Hi Rudic,

Apologies for delayed response
Below is the code im executing for below sample files.

#!/bin/bash
awk -F"|" 'NR==FNR {EXR[$1$2] = $3; next} $13 in EXR {$63 = $38 * EXR[$1]}1' OFS="|" VBRPpipe.csv Foutputp16.csv

Here in File1( Foutputp16.csv ) not able to get result of multiplcation stored in column63 or in either column38

Foutputp16.csv

SourceIifier|SourleName|GntCode|Dision|Suvision|ProfitCe1|Profie2|Plade|Retuiod|SuppliN|DocType|Suppe|Docummber|Docte|Originer|OrigDate|CRDST|LineNumber|CustoN|UINorComposition|OriginaN|Custoame|Custoe|BillTe|Shite|POS|PortCode|ShippingBillNumber|ShippingBillDate|FOB|ExportDuty|HSNorSAC|ProductCode|ProductDescription|Categorduct|UnitOement|Quantity|Taxabue|Integratede|Integratount|Centraate|CentralTt|StaURate|StateUTTaxAmount|CessRateAdvalorem|CessAmountAdvalorem|CessRateSpecific|CessAmountSpecific|Invoalue|ReverseChargeFlag|TCSFlag|eComGSTIN|ITCFlag|ReasonForCreditDebitNote|AccountingVoucmber|Accountinate|Userdefinedfield1|Userdefinedfield2|Userdefinedfield3|Additionalfield1|Additionalfield2|Additlfield3|Additionalfield4|Additionalfield5
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAACT2T|IN|EXPWT|262881626|02.02.2018||||10||||TVVAHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||1301312397||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAACZT|IV|EXPWT|2627|02.02.2018||||10||||TVVHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||130139||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAAZT|NV|AN|2628|02.02.2018||||20||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||13014||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAA8A1ZT|IN|AX|262881629|02.02.2018||||30||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|16.95|0|0|9|1.53|9|1.53|||||||||||1301312577||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAA8A1ZT|IV|EXPWT|262830|02.02.2018||||40||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||1301312||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAAZT|IN|CN|2628816261050|02.02.2018||||11||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8|0|0|9|0.76|9|0.76|||||||||||1301312397||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|081ZT|IV|AN|2628816271060|02.02.2018||||12||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||1301312569||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AA1ZT|NV|AN|2628816282070|02.02.2018||||13||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||1301312574||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAA1ZT|IN|AX|2628816293080|02.02.2018||||14||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|16.95|0|0|9|1.53|9|1.53|||||||||||1301312577||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAACZT|IV|AX|2628816304090|02.02.2018||||15||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||130122||ZEVD|1210||||||0

VBRPpipe.csv

26288162610|50|2|8.47|1.52|262881492
26288162710|60|1|8.47|1.52|262881459
26288162820|70|45|8.47|1.52|262881489
26288162930|80|1|16.95|3.06|262881461
26288163040|90|5|8.47|1.52|262881467

rdrtx1 · April 3, 2018, 2:39pm

maybe:

$63 = $38 * EXR[$13]

as7951 · April 4, 2018, 2:55am

Hi Rudic,

Still not luck, not getting any multiplication result, column 63 is blank only..
can you please provide me any solution for this..
Have you tried this at your end.

RudiC · April 4, 2018, 3:02am

With the correction posted by rdrtx1 it works for me. Show the malfunctioning code and the result if applied to your sample in post#9.

as7951 · April 4, 2018, 3:56am

Hi Rudic,
Apologies, got the correct output now.
but have one doubt..

For the below input, ouput seems to be fine expect for row1 data, as in output after multiplication getting 16 instead of 16.94

SourceIifier|SourleName|GntCode|Dision|Suvision|ProfitCe1|Profie2|Plade|Retuiod|SuppliN|DocType|Suppe|Docummber|Docte|Originer|OrigDate|CRDST|LineNumber|CustoN|UINorComposition|OriginaN|Custoame|Custoe|BillTe|Shite|POS|PortCode|ShippingBillNumber|ShippingBillDate|FOB|ExportDuty|HSNorSAC|ProductCode|ProductDescription|Categorduct|UnitOement|Quantity|Taxabue|Integratede|Integratount|Centraate|CentralTt|StaURate|StateUTTaxAmount|CessRateAdvalorem|CessAmountAdvalorem|CessRateSpecific|CessAmountSpecific|Invoalue|ReverseChargeFlag|TCSFlag|eComGSTIN|ITCFlag|ReasonForCreditDebitNote|AccountingVoucmber|Accountinate|Userdefinedfield1|Userdefinedfield2|Userdefinedfield3|Additionalfield1|Additionalfield2|Additlfield3|Additionalfield4|Additionalfield5
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAACT2T|IN|EXPWT|262881626|02.02.2018||||10||||TVVAHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||1301312397||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAACZT|IV|EXPWT|2627|02.02.2018||||10||||TVVHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||130139||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAAZT|NV|AN|2628|02.02.2018||||20||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||13014||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAA8A1ZT|IN|AX|262881629|02.02.2018||||30||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|16.95|0|0|9|1.53|9|1.53|||||||||||1301312577||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAA8A1ZT|IV|EXPWT|262830|02.02.2018||||40||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||1301312||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAAZT|IN|CN|2628816261050|02.02.2018||||11||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8|0|0|9|0.76|9|0.76|||||||||||1301312397||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|081ZT|IV|AN|2628816271060|02.02.2018||||12||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||1301312569||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AA1ZT|NV|AN|2628816282070|02.02.2018||||13||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||1301312574||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAA1ZT|IN|AX|2628816293080|02.02.2018||||14||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|16.95|0|0|9|1.53|9|1.53|||||||||||1301312577||ZEVD|1210||||||0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAACZT|IV|AX|2628816304090|02.02.2018||||15||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||130122||ZEVD|1210||||||0

getting below output values as 16(marked in red) for column63 row1
So here 8.47 after multiplying by 2 supposed to give 16.94 but instead it returning 16.
but for other row output is fine with decimal values

SAP|SAP_OSR_INV|||||||date+%m%Y|08AAAZT|IN|CN|2628816261050|02.02.2018||||11||||TVHVAILI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8|0|0|9|0.76|9|0.76|||||||||||1301312397||ZEVD|1210|||||16|0
SAP|SAP_OSR_INV|||||||date+%m%Y|081ZT|IV|AN|2628816271060|02.02.2018||||12||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||1301312569||ZEVD|1210|||||8.47|0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AA1ZT|NV|AN|2628816282070|02.02.2018||||13||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||1301312574||ZEVD|1210|||||381.15|0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAA1ZT|IN|AX|2628816293080|02.02.2018||||14||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|16.95|0|0|9|1.53|9|1.53|||||||||||1301312577||ZEVD|1210|||||16.95|0
SAP|SAP_OSR_INV|||||||date+%m%Y|08AAACZT|IV|AX|2628816304090|02.02.2018||||15||||TVHVAISHALI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8.47|0|0|9|0.76|9|0.76|||||||||||130122||ZEVD|1210|||||42.35|0

RudiC · April 4, 2018, 4:15am

I'm not a believer in "selective arithmetics". To me, 8 * 2 = 16 seems correct, no?

SAP|SAP_OSR_INV|||||||date+%m%Y|08AAAZT|IN|CN|2628816261050|02.02.2018||||11||||TVHVAILI|1151040011|8|8|8||||||9984|EVD0|EVDCOCOCDMAMaterial|||0|8|0|0|9|0.76|9|0.76|||||||||||1301312397||ZEVD|1210|||||16|0

as7951 · April 4, 2018, 5:41am

Hi Rudic,
You are right, getting correct output..
sorry..i think sleepless nights have made me Mad..
Apologies again...