Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Compare sum of two columns if variance is zero do nothing else send an email

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Compare sum of two columns if variance is zero do nothing else send an email
# 1  
Old 05-31-2018
Compare sum of two columns if variance is zero do nothing else send an email
11☺Hi,

I have to data sets:
One is in .txt format and other is in .csv format, please refer below two outputs from two files.
File1.txt
Code:
SOURCE    PAYDATE                    TOTAL_DOLLARS   RECORD_COUNT
ASSET     05/25/2018                     247643.94          
ASSET     06/20/2018                     215000.00          
ASSET     06/23/2018                     431454.97          
Total:                                   894098.91        25
EXPNS     05/13/2018                       2257.81          
EXPNS     05/14/2018                       1600.00          
EXPNS     05/17/2018                     366434.88          
EXPNS     05/18/2018                        716.82          
EXPNS     05/21/2018                        798.90          
EXPNS     05/22/2018                      12756.92          
EXPNS     05/23/2018                       8350.10          
EXPNS     05/24/2018                      23792.00          
EXPNS     05/27/2018                        671.45          
EXPNS     06/06/2018                         27.02          
EXPNS     06/20/2018                      22580.19          
EXPNS     06/21/2018                         80.82          
EXPNS     06/22/2018                         70.38          
EXPNS     06/23/2018                      30272.07          
EXPNS     07/08/2018                       6450.00          
EXPNS     07/18/2018                        822.00          
EXPNS     07/23/2018                       3260.00          
Total:                                   480941.36       123
SERVS     05/24/2018                      64983.35          
SERVS     06/18/2018                        260.00          
SERVS     06/20/2018                      11700.00          
SERVS     06/23/2018                       6240.00          
SERVS     07/08/2018                       1070.00          
Total:                                    84253.35        10


File2.csv

Code:
PaymentId    InvoiceReconciliationId    PurchasingUnit    InvoiceNumber    InvoiceDate    Supplier    GrossAmount    GrossAmountCurrency    PaymentTerms    ForTaxAccrual    SupplierLocation    SupplierLocationContactID
PAY212636A-204510    IR212636A-204510    212636A    ########    308858    80.82    USD    Net 30    No    308858    308858
PAYMVH2616-205631    IRMVH2616-205631    MVH2616    ########    5002362    310.25    USD    Net 30    No    5002362    5002362
PAY7799-205021    IR7799-205021    7799    ########    VM1034388    1595    USD    Net 0    No    VM1034388    VM1034388
PAY7798-205010    IR7798-205010    7798    ########    VM1034388    4565    USD    Net 0    No    VM1034388    VM1034388
PAY7801-205552    IR7801-205552    7801    ########    VM1034388    22500    USD    Net 0    No    VM1034388    VM1034388
PAY217758-199795    IR217758-199795    217758    ########    492599    2257.81    USD    Net 02    No    492599    492599
PAY7800-205549    IR7800-205549    7800    ########    VM1034388    1292    USD    Net 0    No    VM1034388    VM1034388
PAYMVH3247-205632    IRMVH3247-205632    MVH3247    ########    5002362    197.6    USD    Net 30    No    5002362    5002362
PAY4821166-204826    IR4821166-204826    4821166    ########    308120    590.1    USD    Net 0    No    308120    308120
PAY195110-204819    IR195110-204819    195110    ########    308120    70.38    USD    Net 30    No    308120    308120
PAY4603-205637    IR4603-205637    4603    ########    488634    6240    USD    Net 30    No    488634    488634
PAY29864A-205651    IR29864A-205651    29864A    ########    VM1041035    6450    USD    Net 45    No    VM1041035    VM1041035
PAY9000547966-205652    IR9000547966-205652    9000547966    ########    EFT0441195    168750    USD    Net 30    No    EFT0441195    EFT0441195
PAY60048606-205653    IR60048606-205653    60048606    ########    333366    1260    USD    Net 30    No    333366    333366
PAY60040467A-205655    IR60040467A-205655    60040467A    ########    333366    1047.28    USD    Net 30    No    333366    333366
PAY60048766-205656    IR60048766-205656    60048766    ########    333366    276.04    USD    Net 30    No    333366    333366
PAY60040469A-205659    IR60040469A-205659    60040469A    ########    333366    1110.12    USD    Net 30    No    333366    333366
PAY60040474A-205661    IR60040474A-205661    60040474A    ########    333366    1153.06    USD    Net 30    No    333366    333366
PAY60038615-205665    IR60038615-205665    60038615    ########    333366    132.59    USD    Net 30    No    333366    333366
PAYMVH5286-205666    IRMVH5286-205666    MVH5286    ########    5002362    140.57    USD    Net 30    No    5002362    5002362
PAY60038616-205668    IR60038616-205668    60038616    ########    333366    132.59    USD    Net 30    No    333366    333366
PAY60041329-205669    IR60041329-205669    60041329    ########    333366    143.13    USD    Net 30    No    333366    333366
PAY60038618-205671    IR60038618-205671    60038618    ########    333366    117.3    USD    Net 30    No    333366    333366
PAY60040473A-205672    IR60040473A-205672    60040473A    ########    333366    3141.84    USD    Net 30    No    333366    333366
PAY60049498-205674    IR60049498-205674    60049498    ########    333366    84.53    USD    Net 30    No    333366    333366
PAY60049495-205673    IR60049495-205673    60049495    ########    333366    85.81    USD    Net 30    No    333366    333366
PAY60041006-205675    IR60041006-205675    60041006    ########    333366    5794.05    USD    Net 30    No    333366    333366
PAY60049506-205676    IR60049506-205676    60049506    ########    333366    84.53    USD    Net 30    No    333366    333366
PAY60049510-205677    IR60049510-205677    60049510    ########    333366    85.81    USD    Net 30    No    333366    333366
PAY60049505-205678    IR60049505-205678    60049505    ########    333366    84.73    USD    Net 30    No    333366    333366
PAY60049516-205679    IR60049516-205679    60049516    ########    333366    83.94    USD    Net 30    No    333366    333366
PAY60049526-205680    IR60049526-205680    60049526    ########    333366    85.52    USD    Net 30    No    333366    333366
PAY60027043A-205681    IR60027043A-205681    60027043A    ########    333366    1047.28    USD    Net 30    No    333366    333366
PAY60049524-205682    IR60049524-205682    60049524    ########    333366    83.74    USD    Net 30    No    333366    333366
PAY60033225A-205684    IR60033225A-205684    60033225A    ########    333366    1047.28    USD    Net 30    No    333366    333366
PAY60049540-205685    IR60049540-205685    60049540    ########    333366    83.35    USD    Net 30    No    333366    333366
PAY60049538-205683    IR60049538-205683    60049538    ########    333366    286.76    USD    Net 30    No    333366    333366
PAY60049536-205686    IR60049536-205686    60049536    ########    333366    85.12    USD    Net 30    No    333366    333366
PAY60049559-205687    IR60049559-205687    60049559    ########    333366    84.53    USD    Net 30    No    333366    333366
PAY60049338-205688    IR60049338-205688    60049338    ########    333366    134.69    USD    Net 30    No    333366    333366
PAY60049554-205689    IR60049554-205689    60049554    ########    333366    227.33    USD    Net 30    No    333366    333366
PAY60049339-205690    IR60049339-205690    60049339    ########    333366    134.69    USD    Net 30    No    333366    333366
PAYMVH7303-205691    IRMVH7303-205691    MVH7303    ########    5002362    175.08    USD    Net 30    No    5002362    5002362
PAY60049567-205692    IR60049567-205692    60049567    ########    333366    169.47    USD    Net 30    No    333366    333366
PAYMVH7341-205694    IRMVH7341-205694    MVH7341    ########    5002362    80.45    USD    Net 30    No    5002362    5002362
PAY60049342-205693    IR60049342-205693    60049342    ########    333366    135    USD    Net 30    No    333366    333366
PAYMVH7507-205698    IRMVH7507-205698    MVH7507    ########    5002362    37.3    USD    Net 30    No    5002362    5002362
PAY51862R-204288    IR51862R-204288    51862R    ########    308298    7250.1    USD    Net 0    No    308298    308298
PAY51867R-204233    IR51867R-204233    51867R    ########    308298    2236.16    USD    Net 0    No    308298    308298
PAY51411R-204232    IR51411R-204232    51411R    ########    308298    2397.04    USD    Net 0    No    308298    308298
PAY60039292A-205700    IR60039292A-205700    60039292A    ########    333366    1120.59    USD    Net 30    No    333366    333366
PAY9887-205703    IR9887-205703    9887    ########    471121    215000    USD    Net 27    No    471121    471121
PAY60039258-205704    IR60039258-205704    60039258    ########    333366    113.19    USD    Net 30    No    333366    333366
PAY60039293A-205705    IR60039293A-205705    60039293A    ########    333366    1133.68    USD    Net 30    No    333366    333366
PAY4593-204153    IR4593-204153    4593    ########    212291    129.53    USD    Net 0    No    212291    212291
PAY4650-204148    IR4650-204148    4650    ########    212291    650.77    USD    Net 0    No    212291    212291
PAY4545-204149    IR4545-204149    4545    ########    212291    93.32    USD    Net 0    No    212291    212291
PAY1360-205709    IR1360-205709    1360    ########    VM1033969    200    USD    Net 45    No    VM1033969    VM1033969
PAY0408497-200596    IR0408497-200596    408497    ########    295247    1600    USD    Net 0    No    295247    295247
PAY1242227-199340    IR1242227-199340    1242227    ########    318108    27.02    USD    Net 27    No    318108    318108
PAYMVH9385-205719    IRMVH9385-205719    MVH9385    ########    5002362    273.03    USD    Net 30    No    5002362    5002362
PAYMVH9389-205718    IRMVH9389-205718    MVH9389    ########    5002362    140.57    USD    Net 30    No    5002362    5002362
PAY133079-205723    IR133079-205723    133079    ########    461155    937.5    USD    Net 30    No    461155    461155
PAYMVJ0773-205727    IRMVJ0773-205727    MVJ0773    ########    5002362    182.54    USD    Net 30    No    5002362    5002362
PAY5802208-205730    IR5802208-205730    5802208    ########    325846    193866.67    USD    Net 30    No    325846    325846
PAYCBR550761-204403    IRCBR550761-204403    CBR550761    ########    499834    260    USD    Net 27    No    499834    499834
PAY5808474-205734    IR5808474-205734    5808474    ########    325846    35000    USD    Net 30    No    325846    325846
PAYMVJ1560-205735    IRMVJ1560-205735    MVJ1560    ########    5002362    115.22    USD    Net 30    No    5002362    5002362
PAY24334A-205731    IR24334A-205731    24334A    ########    411746    650    USD    Net 60    No    411746    411746
PAY24439A-205729    IR24439A-205729    24439A    ########    411746    2610    USD    Net 60    No    411746    411746
PAY685361-205136    IR685361-205136    685361    ########    493989    205.4    USD    Net 04    No    493989    493989
PAYMVJ1701-205736    IRMVJ1701-205736    MVJ1701    ########    5002362    162.84    USD    Net 30    No    5002362    5002362
PAYMVJ2316-205737    IRMVJ2316-205737    MVJ2316    ########    5002362    129.92    USD    Net 30    No    5002362    5002362
PAYMVJ2472-205739    IRMVJ2472-205739    MVJ2472    ########    5002362    122.42    USD    Net 30    No    5002362    5002362
PAY60050351-205740    IR60050351-205740    60050351    ########    333366    390    USD    Net 30    No    333366    333366
PAY60050546-205742    IR60050546-205742    60050546    ########    333366    1251.9    USD    Net 30    No    333366    333366
PAY60050550-205741    IR60050550-205741    60050550    ########    333366    1150.69    USD    Net 30    No    333366    333366
PAY60050616-205743    IR60050616-205743    60050616    ########    333366    205.03    USD    Net 30    No    333366    333366
PAY0000034556-205744    IR0000034556-205744    34556    ########    450717    770    USD    Net 00    No    450717    450717
PAYMVJ3013-205746    IRMVJ3013-205746    MVJ3013    ########    5002362    16.52    USD    Net 30    No    5002362    5002362
PAY920033491-202835    IR920033491-202835    920033491    ########    318888    297    USD    Net 0    No    318888    318888
PAY3028450-202964    IR3028450-202964    3028450    ########    331377    419.82    USD    Net 0    No    331377    331377
PAYMVJ4113-205756    IRMVJ4113-205756    MVJ4113    ########    5002362    37.3    USD    Net 30    No    5002362    5002362
PAYMVJ4352-205759    IRMVJ4352-205759    MVJ4352    ########    5002362    37.57    USD    Net 30    No    5002362    5002362
PAYMVJ4384-205760    IRMVJ4384-205760    MVJ4384    ########    5002362    67.55    USD    Net 30    No    5002362    5002362
PAY1365-205758    IR1365-205758    1365    ########    VM1033969    290    USD    Net 45    No    VM1033969    VM1033969
PAY1361-205757    IR1361-205757    1361    ########    VM1033969    290    USD    Net 45    No    VM1033969    VM1033969
PAY1366-205761    IR1366-205761    1366    ########    VM1033969    290    USD    Net 45    No    VM1033969    VM1033969
PAYMVJ4533-205763    IRMVJ4533-205763    MVJ4533    ########    5002362    3139.76    USD    Net 30    No    5002362    5002362
PAY920033184A-203536    IR920033184A-203536    920033184A    ########    318888    798.9    USD    Net 0    No    318888    318888
PAYW6038487-203841    IRW6038487-203841    W6038487    ########    325644    11475.13    USD    Net 30    No    325644    325644
PAYW6027464-203851    IRW6027464-203851    W6027464    ########    325644    11105.06    USD    Net 30    No    325644    325644
PAY7930406-204146    IR7930406-204146    7930406    ########    504005    822    USD    Net 57    No    504005    504005
PAY40532A-204984    IR40532A-204984    40532A    ########    326954    1600    USD    Net 0    No    326954    326954
PAYMVJ5604-205771    IRMVJ5604-205771    MVJ5604    ########    5002362    39.04    USD    Net 30    No    5002362    5002362
PAYMVJ6431-205773    IRMVJ6431-205773    MVJ6431    ########    5002362    53.93    USD    Net 30    No    5002362    5002362
PAY60051164-205776    IR60051164-205776    60051164    ########    333366    28.69    USD    Net 30    No    333366    333366
PAY60051163-205777    IR60051163-205777    60051163    ########    333366    269.03    USD    Net 30    No    333366    333366
PAY60051167-205778    IR60051167-205778    60051167    ########    333366    34    USD    Net 30    No    333366    333366
PAY60051115-205779    IR60051115-205779    60051115    ########    333366    314.6    USD    Net 30    No    333366    333366
PAY60051124-205780    IR60051124-205780    60051124    ########    333366    133.75    USD    Net 30    No    333366    333366
PAY60051169-205782    IR60051169-205782    60051169    ########    333366    12.77    USD    Net 30    No    333366    333366
PAY60051170-205781    IR60051170-205781    60051170    ########    333366    1191.3    USD    Net 30    No    333366    333366
PAY60051168-205783    IR60051168-205783    60051168    ########    333366    96.3    USD    Net 30    No    333366    333366
PAY60051182-205785    IR60051182-205785    60051182    ########    333366    1172.35    USD    Net 30    No    333366    333366
PAYMVJ8657-205788    IRMVJ8657-205788    MVJ8657    ########    5002362    62.88    USD    Net 30    No    5002362    5002362
PAY4602-205639    IR4602-205639    4602    ########    488634    6084    USD    Net 27    No    488634    488634
PAY4604-205643    IR4604-205643    4604    ########    488634    5616    USD    Net 27    No    488634    488634
PAYMVJ9950-205791    IRMVJ9950-205791    MVJ9950    ########    5002362    1063.44    USD    Net 30    No    5002362    5002362
PAYMVK0039-205792    IRMVK0039-205792    MVK0039    ########    5002362    638.48    USD    Net 30    No    5002362    5002362
PAYMVK0642-205794    IRMVK0642-205794    MVK0642    ########    5002362    317.24    USD    Net 30    No    5002362    5002362
PAY60051451-205796    IR60051451-205796    60051451    ########    333366    22.13    USD    Net 30    No    333366    333366
PAY60051965-205797    IR60051965-205797    60051965    ########    333366    7353.35    USD    Net 30    No    333366    333366
PAYMVK1785-205798    IRMVK1785-205798    MVK1785    ########    5002362    3773.46    USD    Net 30    No    5002362    5002362
PAY685358-205131    IR685358-205131    685358    ########    493989    466.05    USD    Net 04    No    493989    493989
PAYCI2006402-205753    IRCI2006402-205753    CI2006402    ########    321735    64213.35    USD    Net 0    No    321735    321735
PAYMVK5993-205808    IRMVK5993-205808    MVK5993    ########    5002362    3709.55    USD    Net 30    No    5002362    5002362
PAYMVK8369-205813    IRMVK8369-205813    MVK8369    ########    5002362    1.64    USD    Net 30    No    5002362    5002362
PAYMVK8378-205815    IRMVK8378-205815    MVK8378    ########    5002362    1.76    USD    Net 30    No    5002362    5002362
PAYMVK8363-205814    IRMVK8363-205814    MVK8363    ########    5002362    5.9    USD    Net 30    No    5002362    5002362
PAYMVK8904-205821    IRMVK8904-205821    MVK8904    ########    5002362    37.11    USD    Net 30    No    5002362    5002362
PAYMVK9030-205823    IRMVK9030-205823    MVK9030    ########    5002362    12.12    USD    Net 30    No    5002362    5002362
PAYMVK9993-205827    IRMVK9993-205827    MVK9993    ########    5002362    295    USD    Net 30    No    5002362    5002362
PAYMVL2121-205829    IRMVL2121-205829    MVL2121    ########    5002362    60.83    USD    Net 30    No    5002362    5002362
PAY60052441-205831    IR60052441-205831    60052441    ########    333366    184.04    USD    Net 30    No    333366    333366
PAY60052568-205832    IR60052568-205832    60052568    ########    333366    1537.59    USD    Net 30    No    333366    333366
PAY60052273-205830    IR60052273-205830    60052273    ########    333366    755.66    USD    Net 30    No    333366    333366
PAY60052379-205833    IR60052379-205833    60052379    ########    333366    135.31    USD    Net 30    No    333366    333366
PAY60052594-205834    IR60052594-205834    60052594    ########    333366    734.4    USD    Net 30    No    333366    333366
PAYMVL3046-205835    IRMVL3046-205835    MVL3046    ########    5002362    26.96    USD    Net 30    No    5002362    5002362
PAYMVL3065-205836    IRMVL3065-205836    MVL3065    ########    5002362    132.93    USD    Net 30    No    5002362    5002362
PAYMVL3098-205837    IRMVL3098-205837    MVL3098    ########    5002362    62.55    USD    Net 30    No    5002362    5002362
PAYMVL3095-205838    IRMVL3095-205838    MVL3095    ########    5002362    104.43    USD    Net 30    No    5002362    5002362
PAYMVL3102-205839    IRMVL3102-205839    MVL3102    ########    5002362    62.55    USD    Net 30    No    5002362    5002362
PAYMVL3105-205840    IRMVL3105-205840    MVL3105    ########    5002362    63.44    USD    Net 30    No    5002362    5002362
PAYMVL3790-205842    IRMVL3790-205842    MVL3790    ########    5002362    322.02    USD    Net 30    No    5002362    5002362
PAYMVL3788-205841    IRMVL3788-205841    MVL3788    ########    5002362    126.88    USD    Net 30    No    5002362    5002362
PAY60052683-205844    IR60052683-205844    60052683    ########    333366    40.74    USD    Net 30    No    333366    333366
PAY60052748-205845    IR60052748-205845    60052748    ########    333366    883    USD    Net 30    No    333366    333366
PAY60052681-205846    IR60052681-205846    60052681    ########    333366    157.25    USD    Net 30    No    333366    333366
PAY60052703-205847    IR60052703-205847    60052703    ########    333366    64.2    USD    Net 30    No    333366    333366
PAY60052724-205848    IR60052724-205848    60052724    ########    333366    980.75    USD    Net 30    No    333366    333366
PAY60052727-205849    IR60052727-205849    60052727    ########    333366    39.41    USD    Net 30    No    333366    333366
PAY60052717-205850    IR60052717-205850    60052717    ########    333366    327.29    USD    Net 30    No    333366    333366
PAYMVL6218-205852    IRMVL6218-205852    MVL6218    ########    5002362    79.7    USD    Net 30    No    5002362    5002362
PAYMVL8213-205853    IRMVL8213-205853    MVL8213    ########    5002362    64.19    USD    Net 30    No    5002362    5002362
PAYMVL9039-205854    IRMVL9039-205854    MVL9039    ########    5002362    356.89    USD    Net 30    No    5002362    5002362
PAYMVL9282-205855    IRMVL9282-205855    MVL9282    ########    5002362    1.77    USD    Net 30    No    5002362    5002362
PAY519000515-202253    IR519000515-202253    519000515    ########    436346    366434.88    USD    Net 0    No    436346    436346
PAY60040462A-205856    IR60040462A-205856    60040462A    ########    333366    1117.97    USD    Net 30    No    333366    333366
PAYMVM2042-205857    IRMVM2042-205857    MVM2042    ########    5002362    259.9    USD    Net 30    No    5002362    5002362
PAY60040463A-205858    IR60040463A-205858    60040463A    ########    333366    1110.12    USD    Net 30    No    333366    333366
PAY60040464A-205859    IR60040464A-205859    60040464A    ########    333366    1133.44    USD    Net 30    No    333366    333366
PAY60040465A-205860    IR60040465A-205860    60040465A    ########    333366    1120.59    USD    Net 30    No    333366    333366
PAY60027791A-205861    IR60027791A-205861    60027791A    ########    333366    1137.35    USD    Net 30    No    333366    333366
PAYMVM3315-205862    IRMVM3315-205862    MVM3315    ########    5002362    118.18    USD    Net 30    No    5002362    5002362
PAYMVN0804-205863    IRMVN0804-205863    MVN0804    ########    5002362    77.04    USD    Net 30    No    5002362    5002362
PAYP2DIRTTAPR-205864    IRP2DIRTTAPR-205864    P2DIRTTAPR    ########    77P0453    247643.94    USD    Net 0    No    77P0453    77P0453


Sum of all the totals in first file has to be comapred with the total sum of Gross Amount(i.e 7th column).
If the variance is Zero do nothing else "Variance is not zero"


Moderator's Comments:
Mod Comment Please use CODE tags for data data as well as required by forum rules!
Last edited by RudiC; 05-31-2018 at 06:22 AM.. Reason: Added CODE tags.
# 2  
Old 05-31-2018
Welcome to the forum.

Any attempts / ideas / thoughts from your side?

Please be aware that $7 seems to be the GrossAmountCurrency...
# 3  
Old 05-31-2018
Where Ariba is File1 and Pay is File2 as per actual post
Yes, I did!

I'm still a Layman but here is what I tried.

Code:
#!/bin/bash/sh
a=0.000000
for value in `cat Ariba.csv  | grep Total | tr -s " " " " | cut -d " " -f2`
do
a=$(echo $a + $value | bc)
done
echo $a
b=0.000000
for value2 in `cat Pay.csv | cut -d "," -f7 | sed -e 's/^"//g' -e 's/"$//g'`
do
b=$(echo $b + $value2 | bc)
done
echo $b
if [ $a = $b ]
then
echo "Total is same"
else
echo "Mismatch in total"
fi

---------- Post updated at 09:42 PM ---------- Previous update was at 09:40 PM ----------

I also tried this...

Code:
sed '1d' Pay.csv > tmpfile; mv tmpfile Pay.csv
awk -F, '{total+= $7;print (total)}' Pay.csv >Total1.txt



file1="Ariba.txt"

while IFS= read -r line
do
                a="$(cut -d'' -f1 <<<"$line")"
                if [ "$a" = "Total:" ]
                then
                                b="$(cut -d'' -f4 <<<"$line")"
                                echo "$b"
                                var1=$(( $var1 + $b ))
                fi
done <"$file1"

echo "Total from Ariba.csv is : $var1" >Total2.txt

Where Ariba is file1 and Pay is File2
Last edited by Tahir_M; 05-31-2018 at 01:13 PM.. Reason: adding file names
# 4  
Old 05-31-2018
Thanks for sharing! How about
Code:
awk '
FNR == 1        {next
                }
FNR == NR       {if (/^Total/) SUM += $2
                 next
                }
                {SUM -= $6
                }
END             {if (SUM >= 0.01) print "Variance is not zero: ", SUM
                }
 ' file1 file2

There's a small rounding (?) / conversion error in the order of 1E-9, so the END section doesn't check for zero but a value less than a cent.


EDIT: That rounding / conversion error can be eliminated by calculating with integers only:
Code:
awk '
FNR == 1        {next
                }
FNR == NR       {if (/^Total/) SUM += $2*100
                 next
                }
                {SUM -= $6*100
                }
END             {if (SUM) print "Variance is not zero: ", SUM/100
                }
' file1 file2

Last edited by RudiC; 05-31-2018 at 01:40 PM..
This User Gave Thanks to RudiC For This Post:
Tahir_M
# 5  
Old 05-31-2018
Replying:
The two files don't have a variance.

But the code shows a huge Variance?!
# 6  
Old 05-31-2018
No, it doesn't.
# 7  
Old 06-01-2018
Please refer to this, let me know if i'm doing anything wrong?

Code:
cat Test1.sh
awk '
FNR == 1        {next
                }
FNR == NR       {if (/^Total/) SUM += $2*100
                 next
                }
                {SUM -= $6*100
                }
END             {if (SUM) print "Variance is not zero: ", SUM/100
                }
' Ariba.csv Pay.csv

cat Test2.sh
awk '
FNR == 1        {next
                }
FNR == NR       {if (/^Total/) SUM += $2
                 next
                }
                {SUM -= $6
                }
END             {if (SUM >= 0.01) print "Variance is not zero: ", SUM
                }
' Ariba.csv Pay.csv

O/P

Code:
./Test1.sh
Variance is not zero:  2.23578e+06
./Test2.sh
 Variance is not zero:  2.23578e+06

Moderator's Comments:
Mod Comment SERIOUSLY: Please use CODE tags as required by forum rules!
Last edited by RudiC; 06-01-2018 at 07:26 AM.. Reason: Added CODE tags.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Group by columns and add sum in new columns

Dear Experts, I have input file which is comma separated, has 4 columns like below, BRAND,COUNTRY,MODEL,COUNT NIKE,USA,DUMMY,5 NIKE,USA,ORIGINAL,10 PUMA,FRANCE,DUMMY,20 PUMA,FRANCE,ORIGINAL,15 ADIDAS,ITALY,DUMMY,50 ADIDAS,ITALY,ORIGINAL,50 SPIKE,CHINA,DUMMY,1O And expected output add... (2 Replies)
Discussion started by: ricky1991
2 Replies

2. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns satisfy the condition

HI All, I'm embedding SQL query in Script which gives following output: Assignee Group Total ABC Group1 17 PQR Group2 5 PQR Group3 6 XYZ Group1 10 XYZ Group3 5 I have saved the above output in a file. How do i sum up the contents of this output so as to get following output: ... (4 Replies)
Discussion started by: Khushbu
4 Replies

3. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

4. Shell Programming and Scripting

Get the SUM of TWO columns SEPARATELY by doing GROUP BY on other columns

My File looks like: "|" -> Field separator A|B|C|100|1000 D|E|F|1|2 G|H|I|0|7 D|E|F|1|2 A|B|C|10|10000 G|H|I|0|7 A|B|C|1|100 D|E|F|1|2 I need to do a SUM on Col. 5 and Col.6 by grouping on Col 1,2 & 3 My expected output is: A|B|C|111|11100 (2 Replies)
Discussion started by: machomaddy
2 Replies

5. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns match on two rows

Hi all, I know this sounds suspiciously like a homework course; but, it is not. My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I... (6 Replies)
Discussion started by: mtucker6784
6 Replies

6. UNIX for Dummies Questions & Answers

new to ldap, send email to a ou or group, and see a list from email client

hi, i'm running openldap on ubuntu 10.04, creating new items with apache directory studio (windows version). i use the ldap just as an address book to our small office (email clients are windows live mail 2009, 2011, microsoft outlook 2007 and 2010). a. i cant see a list of the contacts,... (0 Replies)
Discussion started by: V4705
0 Replies

7. Shell Programming and Scripting

Script to send email after comparing the folder permissions to a certain permission & send email

Hello , I am trying to write a unix shell script to compare folder permission to say drwxr-x-wx and then send an email to my id in case the folders don't have the drwxr-x-wx permissions set for them . I have been trying to come up with a script for few days now , pls help me:( (2 Replies)
Discussion started by: nairshar
2 Replies

8. Solaris

Send an email from Solaris using Linux email server

Hello everyone I have a problem and I need your help: I have a Solaris 10 and Solaris 8 UNIX Servers, and Linux Centos4 as email server. I need send an email from Solaris servers preferably using Centos4 email server. I have no mail service configured in my Solaris computers (1 Reply)
Discussion started by: aflores
1 Replies

9. Shell Programming and Scripting

Sum of three columns - in 4N columns file

Hi All, happy new year. I have a file with 4xN columns like 0.0000e+00 0.0000e+00 7.199E+07 7.123E+07 6.976E+07 6.482E+07 5.256E+07 2.523E+07 0.0000e+00 0.0000e+00 8.641E+07 8.550E+07 8.373E+07 7.780E+07 6.309E+07 3.028E+07... (8 Replies)
Discussion started by: f_o_555
8 Replies

10. UNIX for Advanced & Expert Users

Unable to send eMail from a UNIX-Host ( using mailx ) to a Outlook-email-addres(Win)

Hi A) I am able to send eMail using mailx from a UNIX ( solaris 8 ) host to my Outlook-email-ID : FName.Surname@Citigroup.com ( This is NOT my actual -eMail-ID). But in Outlook the "From :" eMail address is displayed as " usr1@unix-host1.unregistered.email.citicorp.com " .i.e the words... (2 Replies)
Discussion started by: Vetrivela
2 Replies
Login or Register to Ask a Question