Difference script

jjoy · May 4, 2010, 10:18am

Hi All,

I will need a script which takes an difernce of the 3rd column of report generated yesterday with the report generated today and has the differnce as a last colums in a new result file.

 
yday.txt:
UNDOTBS1                                  15.00            10.00        5.00 
UNDOTBS2                                  20.00            15.00        5.00 
USERS                                      5.00             3.00        2.00 

today.txt:
UNDOTBS1                                  15.00            12.00        3.00 
UNDOTBS2                                  20.00            17.00        3.00 
USERS                                      5.00             4.00        1.00 
 
required.txt: (today.txt + column with differnce)
UNDOTBS1                                  15.00            12.00        3.00    2.00
UNDOTBS2                                  20.00            17.00        3.00    2.00
USERS                                      5.00             4.00        1.00    1.00

vidyadhar85 · May 4, 2010, 10:49am

what have you tried till now??

aigles · May 4, 2010, 10:54am

Try and adapt the following AWK command :

awk '
function abs(n) {
   return (n<0 ? -n : n)
}
NR==FNR { Old[$1] = $3 ; next }
{
   diff = ""
   if ($1 in Old && $3 != Old[$1] )
      diff = sprintf("   %5.2f", abs($3-Old[$1]));
   print $0 diff
}
' yday.txt today.txt

Jean-Pierre.

jjoy · May 5, 2010, 2:58am

Hi ,

Thanks for the script , this works perfectly fine, when the digits are 10.00, 15.00 and so on..,
If the number is like 11,229.06, 98,532.02 please let me know what modification the script will need?

Thanks a lot.

aigles · May 5, 2010, 4:43am

Try this new version (the function toStr is a little bit ugly) :

awk '

function abs(n) {
   return (n<0 ? -n : n)
}

function toNum(s) {
   sub(/,/, "", s);
   return s+0;
}

function toStr(n, w, p        , str, sign, len, dec, num) {
   str = sprintf("%." p "f", n+0);
   if (str ~ /^[+-]/) {
      sign = substr(str, 1, 1);
      str  = substr(str,2);
   }
   dec = str;
   sub(/.*\./, "", dec);
   sub(/\..*/, "", str);
   num = "";
   for (len=length(str); len>3; len-=3) {
      num = "," substr(str, len-2, 3) num
   }
   num = substr(str, 1, len) num;
   sub(/^,/, "", num);
   return  sprintf("%" w "s", sign num "." dec);
}

NR==FNR { Old[$1] = toNum($3) ; next }
{
   diff = ""
   new  = toNum($3);
   if ($1 in Old && new != Old[$1] )
      diff = toStr(abs(new-Old[$1]), 15, 2);
   print $0 diff
}
' yday.txt today.txt

yday.txt

UNDOTBS1                                  15.00            10.00        5.00 
UNDOTBS2                                  20.00            15.00        5.00 
FOO                                        1.00             1.00        1.00
USERS                                      5.00             3.00        2.00 
BONUS                                    123.45         1,111.11      200.00

today.txt

UNDOTBS1                                  15.00            12.00        3.00
UNDOTBS2                                  20.00            17.00        3.00
FOO                                        1.00             1.00        1.00
USERS                                      5.00             4.00        1.00
BONUS                                    777.00         3,333.33       33.00

Output:

UNDOTBS1                                  15.00            12.00        3.00           2.00
UNDOTBS2                                  20.00            17.00        3.00           2.00
FOO                                        1.00             1.00        1.00
USERS                                      5.00             4.00        1.00           1.00
BONUS                                    777.00         3,333.33       33.00       2,222.22

Jean-Pierre.

jjoy · May 6, 2010, 6:27am

Hi,

Thanks a lot, this script works real great.
I have just one more addition to this, hopefully i am not asking too much.
Is there any way, that the if the difference can be made 0. Shown it as bold in the code below.

Output:

Code:
UNDOTBS1                                  15.00            12.00        3.00           2.00
UNDOTBS2                                  20.00            17.00        3.00           2.00
FOO                                        1.00             1.00        1.00           0.00
BONUS                                    777.00         3,333.33       33.00       2,222.22

aigles · May 6, 2010, 6:45am

Another new version of the script :

awk '

function abs(n) {
   return (n<0 ? -n : n)
}

function toNum(s) {
   sub(/,/, "", s);
   return s+0;
}

function toStr(n, w, p        , str, sign, len, dec, num) {
   str = sprintf("%." p "f", n+0);
   if (str ~ /^[+-]/) {
      sign = substr(str, 1, 1);
      str  = substr(str,2);
   }
   dec = str;
   sub(/.*\./, "", dec);
   sub(/\..*/, "", str);
   num = "";
   for (len=length(str); len>3; len-=3) {
      num = "," substr(str, len-2, 3) num
   }
   num = substr(str, 1, len) num;
   sub(/^,/, "", num);
   return  sprintf("%" w "s", sign num "." dec);
}

NR==FNR { Old[$1] = toNum($3) ; next }
{
   diff = 0
   new  = toNum($3);
   if ($1 in Old && new != Old[$1] )
      diff = abs(new-Old[$1]);
   diff = toStr(abs(diff), 15, 2);
   print $0,diff
}
' yday.txt today.txt

Output:

UNDOTBS1                                  15.00            12.00        3.00            2.00
UNDOTBS2                                  20.00            17.00        3.00            2.00
FOO                                        1.00             1.00        1.00            0.00
USERS                                      5.00             4.00        1.00            1.00
BONUS                                    777.00         3,333.33       33.00        2,222.22

Jean-Pierre.

jjoy · May 7, 2010, 3:27am

Hi..

It has worked.Thanks a lot. Thanks you very much. I really appreciate your help.:).

---------- Post updated at 04:47 PM ---------- Previous update was at 04:45 PM ----------

Hi..

It has worked.Thanks a lot. Thanks you very much. I really appreciate your help.:).

---------- Post updated 05-07-10 at 12:57 PM ---------- Previous update was 05-06-10 at 04:47 PM ----------

Hi..

I have tried the script on the report, I see that there is a another change that needs to be made.
For the differnce of today-yest , is there any possibilty of having an negative sign , when the value of today.txt is less than that of yest.txt

Thanks once again.

Eg:

today.txt: 1200
yest.txt :  1500
o/p        : -300

aigles · May 7, 2010, 3:52am

awk '

function toNum(s) {
   sub(/,/, "", s);
   return s+0;
}

function toStr(n, w, p        , str, sign, len, dec, num) {
   str = sprintf("%." p "f", n+0);
   if (str ~ /^[+-]/) {
      sign = substr(str, 1, 1);
      str  = substr(str,2);
   }
   dec = str;
   sub(/.*\./, "", dec);
   sub(/\..*/, "", str);
   num = "";
   for (len=length(str); len>3; len-=3) {
      num = "," substr(str, len-2, 3) num
   }
   num = substr(str, 1, len) num;
   sub(/^,/, "", num);
   return  sprintf("%" w "s", sign num "." dec);
}

NR==FNR { Old[$1] = toNum($3) ; next }
{
   diff = 0
   new  = toNum($3);
   if ($1 in Old && new != Old[$1] )
      diff = new-Old[$1];
   diff = toStr(diff, 15, 2);
   print $0,diff
}
' yday.txt today.txt

yday.txt :

UNDOTBS1                                  15.00           110.00        5.00 
UNDOTBS2                                  20.00         2,015.00        5.00 
FOO                                        1.00             1.00        1.00
USERS                                      5.00             3.00        2.00 
BONUS                                    123.45         1,111.11      200.00

today.txt :

UNDOTBS1                                  15.00            12.00        3.00
UNDOTBS2                                  20.00            17.00        3.00
FOO                                        1.00             1.00        1.00
USERS                                      5.00             4.00        1.00
BONUS                                    777.00         3,333.33       33.00

Output :

UNDOTBS1                                  15.00            12.00        3.00          -98.00
UNDOTBS2                                  20.00            17.00        3.00       -1,998.00
FOO                                        1.00             1.00        1.00            0.00
USERS                                      5.00             4.00        1.00            1.00
BONUS                                    777.00         3,333.33       33.00        2,222.22

Jean-Pierre.

jjoy · May 7, 2010, 7:33am

Thankyou again...

---------- Post updated at 05:03 PM ---------- Previous update was at 02:23 PM ----------

Hi,

This script is working fine on Linux, when i did try the same on SunOS, I am getting the below error:

awk: syntax error near line 3
awk: bailing out near line 3

Any Help..

Thanks.

vgersh99 · May 7, 2010, 7:45am

jjoy:

Thankyou again...

---------- Post updated at 05:03 PM ---------- Previous update was at 02:23 PM ----------

Hi,

This script is working fine on Linux, when i did try the same on SunOS, I am getting the below error:
awk: syntax error near line 3
awk: bailing out near line 3
Any Help..

Thanks.

As always.... if on Solaris, use nawk or /usr/bin/xpg4/bin/awk instead of the o' broken '/usr/bin/awk'.

jjoy · May 7, 2010, 7:56am

Thankyou..