Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Sum with condition

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Sum with condition
# 1  
Old 11-25-2014
Sum with condition
Dear masters,

I have file input
Code:
011171646073|12129|12129|A027|20141001|20141015|2014|10|01|2013|10|15
011171646076|12129|12129|A027|20141001|20141012|2014|10|01|2014|09|12
011171646078|12129|12129|A027|20141001|20141015|2014|10|01|2014|10|15
011171646081|12129|12129|A027|20141001|20141016|2014|10|10|2014|10|08
011171646082|12129|12129|A027|20141001|20141014|2014|10|01|2014|10|14

I need to calculate as $13 with condition when column 10 >= column 8 and column 12 >=column 9 and column 13 >=column 10 then do
Code:
((($12-$10)*30) + ($12-$10))

I tried this code but it didnt meet my expectation

Code:
BEGIN { FS=OFS="|"; CONVFMT=OFMT="%.10g" }
$8 >= $11 && $12 >= $9 && $13>=$10 { $13 = ((($12-$10)*30) + ($12-$10)) }
else { $13 = "FALSE" }
{ print }

My Expected output

Code:
011171646073|12129|12129|A027|20141001|20141015|2014|10|01|2013|10|15|14
011171646076|12129|12129|A027|20141001|20141012|2014|10|01|2014|09|12|FALSE
011171646078|12129|12129|A027|20141001|20141015|2014|10|01|2014|10|15|14
011171646081|12129|12129|A027|20141001|20141016|2014|10|10|2014|10|08|FALSE
011171646082|12129|12129|A027|20141001|20141014|2014|10|01|2014|10|14|13

Thanks
# 2  
Old 11-25-2014
Correct your syntax like below, I noticed that your input file contains only 12 fields, so even after syntax correction you will receive column 13 as FALSE, incase if your input is different from what you have shown to us, following might work.

Code:
BEGIN{ 
	FS=OFS="|"; 
	CONVFMT=OFMT="%.10g" 
}
{ 
        # Since NF is only 12, so $13 is FALSE always
	if( $10 >= $8 && $12 >= $9 && $13 >= $10 )
	{ 
		$13 = 30*($12-$10) + $12-$10 
	}
   else { 
		$13 = "FALSE" 
	}

		print
}

Quote:
Originally Posted by radius
Dear masters,

I have file input
Code:
011171646073|12129|12129|A027|20141001|20141015|2014|10|01|2013|10|15
011171646076|12129|12129|A027|20141001|20141012|2014|10|01|2014|09|12
011171646078|12129|12129|A027|20141001|20141015|2014|10|01|2014|10|15
011171646081|12129|12129|A027|20141001|20141016|2014|10|10|2014|10|08
011171646082|12129|12129|A027|20141001|20141014|2014|10|01|2014|10|14

I need to calculate as $13 with condition when column 10 >= column 8 and column 12 >=column 9 and column 13 >=column 10 then do
Code:
((($12-$10)*30) + ($12-$10))

I tried this code but it didnt meet my expectation

Code:
BEGIN { FS=OFS="|"; CONVFMT=OFMT="%.10g" }
$8 >= $11 && $12 >= $9 && $13>=$10 { $13 = ((($12-$10)*30) + ($12-$10)) }
else { $13 = "FALSE" }
{ print }

My Expected output

Code:
011171646073|12129|12129|A027|20141001|20141015|2014|10|01|2013|10|15|14
011171646076|12129|12129|A027|20141001|20141012|2014|10|01|2014|09|12|FALSE
011171646078|12129|12129|A027|20141001|20141015|2014|10|01|2014|10|15|14
011171646081|12129|12129|A027|20141001|20141016|2014|10|10|2014|10|08|FALSE
011171646082|12129|12129|A027|20141001|20141014|2014|10|01|2014|10|14|13

Thanks
# 3  
Old 11-25-2014
There's no field 13, so your condition $13>=$10 will never be true (unless $13 converted to a numeric value by e.g. adding a 0 and $10 is negative or zero).
Why so difficult? Why not ($12 - $10)*31 in lieu of your lengthy formula? And, that will never yield 13 or 14, but values around -62000...
# 4  
Old 11-25-2014
sorry for misstyping
I did this one
Code:
BEGIN { FS=OFS="|"; CONVFMT=OFMT="%.10g" }
$8 >= $11 && $12 >= $9 && $12>=$10 { $13 = (($12-$10)*30) + ($12-$10) }
else { $13 = "FALSE" }
{ print }

Last edited by radius; 11-25-2014 at 10:38 PM..
# 5  
Old 11-25-2014
Radius,

What is column 13 support to be the 'sum' of?

It looks like you are trying to figure out the number of days between a start date (year, month, and day in columns 7, 8, & 9) and an end date (columns 10, 11, & 12), ignore the year.

What would be the expected result for the following examples:
Code:
011108675309|12129|12129|A027|20141001|20141105|2014|10|01|2014|11|05
442079460347|12129|12129|A027|20141230|20150105|2014|12|30|2013|01|05
012126399675|12129|12129|A027|20141230|20150103|2014|12|30|2015|01|03

# 6  
Old 11-26-2014
I want to count the days between $6 and $5
Code:
011108675309|12129|12129|A027|20141001|20141105|2014|10|01|2014|11|05|(31+5)
442079460347|12129|12129|A027|20141230|20150105|2014|12|30|2013|01|05|FALSE
012126399675|12129|12129|A027|20141230|20150103|2014|12|30|2015|01|03|(1+3)

# 7  
Old 11-26-2014
Hmmm. Are fields $7, $8, and $9 just the year, month, and day parts of field $5, and $10, $11, and $12 of field $6? The reason I ask is your example date has fields that do not match:
Code:
011171646073|12129|12129|A027|20141001|20141015|2014|10|01|2013|10|15
011171646076|12129|12129|A027|20141001|20141012|2014|10|01|2014|09|12
011171646078|12129|12129|A027|20141001|20141015|2014|10|01|2014|10|15
011171646081|12129|12129|A027|20141001|20141016|2014|10|10|2014|10|08
011171646082|12129|12129|A027|20141001|20141014|2014|10|01|2014|10|14

So if you want the difference in days between fields $5 and $6:
Code:
IFS="${IFS}|"

cat -s "${@}" \
| while read f1 f2 f3 f4 sdate edate f7 f8 f9 f10 f11 f12; do
    if [[ $sdate -le $edate ]]; then
        delta=$(( ( $(date +%s -d $edate) - $(date +%s -d $sdate) ) / 86400 ))
    else
        delta=FALSE
    fi
    echo "$f1|$f2|$f3|$f4|$sdate|$edate|$f7|$f8|$f9|$f10|$f11|$f12|$delta"
done

which yields:
Code:
011171646073|12129|12129|A027|20141001|20141015|2014|10|01|2013|10|15|14
011171646076|12129|12129|A027|20141001|20141012|2014|10|01|2014|09|12|11
011171646078|12129|12129|A027|20141001|20141015|2014|10|01|2014|10|15|14
011171646081|12129|12129|A027|20141001|20141016|2014|10|10|2014|10|08|15
011171646082|12129|12129|A027|20141001|20141014|2014|10|01|2014|10|14|13
011108675309|12129|12129|A027|20141001|20141105|2014|10|01|2014|11|05|35
442079460347|12129|12129|A027|20141230|20150105|2014|12|30|2013|01|05|6
012126399675|12129|12129|A027|20141230|20150103|2014|12|30|2015|01|03|4

If you want the difference in days between fields $7, $8, & $9 and $10, $11, & $12:
Code:
IFS="${IFS}|"

cat -s "${@}" \
| while read f1 f2 f3 f4 f5 f6 syear smon sday eyear emon eday; do
    sdate=${syear}${smon}${sday}
    edate=${eyear}${emon}${eday}

    if [[ $sdate -le $edate ]]; then
        delta=$(( ( $(date +%s -d $edate) - $(date +%s -d $sdate) ) / 86400 ))
    else
        delta=FALSE
    fi
    echo "$f1|$f2|$f3|$f4|$f5|$f6|$syear|$smon|$sday|$eyear|$emon|$eday|$delta"
done

which yields:
Code:
011171646073|12129|12129|A027|20141001|20141015|2014|10|01|2013|10|15|FALSE
011171646076|12129|12129|A027|20141001|20141012|2014|10|01|2014|09|12|FALSE
011171646078|12129|12129|A027|20141001|20141015|2014|10|01|2014|10|15|14
011171646081|12129|12129|A027|20141001|20141016|2014|10|10|2014|10|08|FALSE
011171646082|12129|12129|A027|20141001|20141014|2014|10|01|2014|10|14|13
011108675309|12129|12129|A027|20141001|20141105|2014|10|01|2014|11|05|35
442079460347|12129|12129|A027|20141230|20150105|2014|12|30|2013|01|05|FALSE
012126399675|12129|12129|A027|20141230|20150103|2014|12|30|2015|01|03|4

Please be aware that this is process intensive. If your dataset is large, you might consider using a perl or python script - something that has built in date conversion functions - if processing time is important.

---------- Post updated at 06:21 AM ---------- Previous update was at 04:21 AM ----------

Perlish solution:
Code:
#! /usr/bin/perl

use strict;
use warnings;
use integer;

use Time::Local;

my %DAY = ();

sub day {
  my $date = shift @_;

  unless (defined $DAY{$date}) {
    my ($y, $m, $d) = $date =~ m{^(\d\d\d\d)(\d\d)(\d\d)$};
    $DAY{$date} = timelocal(0, 0, 0, $d, $m - 1, $y) / 86400;
  }

  return $DAY{$date};
}

$\ = "\n";
$, = '|';

while (<>) {
  chomp;
  my @F = split /\|/;
  print @F, $F[4] le $F[5] ? day($F[5]) - day($F[4]) : 'FALSE';
}

The 'day' function builds up a lookup table of converted dates.
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. 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

2. Shell Programming and Scripting

If condition return 0 even when it fails to satisfy te condition

HI My doubt may be basic one but I need to get it clarified.. When i use "if" condition that checks for many AND, OR logical conditions like if ]; then return 0 fi Even the if condition fails it returns as zero.. Any clue.. But if i add else condition like if ]; ... (2 Replies)
Discussion started by: Priya Amaresh
2 Replies

3. Shell Programming and Scripting

Sum Of Column Based On Column Condition

I have a following inputfile MT,AP,CDM,TTML,MUM,GS,SUCC,3 MT,AP,CDM,TTSL,AP,GS,FAIL,9 MT,AP,CDM,RCom,MAH,GS,SUCC,3 MT,AP,CDM,RTL,HP,GS,SUCC,1 MT,AP,CDM,Uni,UPE,GS,SUCC,2 MT,AP,CDM,Uni,MUM,GS,SUCC,2 TTSL,AP,GS,MT,MAH,CDM,SUCC,20 TTML,AP,GS,MT,MAH,CDM,FAIL,10... (2 Replies)
Discussion started by: siramitsharma
2 Replies

4. Shell Programming and Scripting

Sum with condition

Hi all, I have question on how to sum up a column with "condition" for following listing example imvprd_lun01 34 imvprd_pool01 admin 19 imv1prd_lun01 188 IMBDW_N0 imvprd_pool01 admin 133 imv1prd_lun02 1236 IMBDW_N1 imvprd_pool01 admin 1125 imv1prd_lun03... (2 Replies)
Discussion started by: ckwan
2 Replies

5. Shell Programming and Scripting

How to sum these value.

Hi, Unix Gurus, I need sum values from a file. file format like: 0004004 0000817 0045000 0045000 0045000 0045000 0045000 0045000 0045000 0045000 0045000 0045000 0004406 the result should be 459227 (817+45000 ... + 4406) anybody can help me out (7 Replies)
Discussion started by: ken002
7 Replies

6. Shell Programming and Scripting

redirect stdout echo command in condition A run in condition B

hi, I have some problems in my simple script about the redirect echo stdout command inside a condition. Why is the echo command inside the elif still execute in the else command Here are my simple script After check on the two diff output the echo stdout redirect is present in two diff... (3 Replies)
Discussion started by: jao_madn
3 Replies

7. HP-UX

Difference between [condition] and [[condition]] and ((condition)) when used with if condition

Executed the following if conditions .. and got different results . only (( )) gave correct o/p with all scenarios . Can anybody please let me know what is the difference between and ] and ((condition)) when used with if condition. And why each condition gave different result. 1.... (2 Replies)
Discussion started by: soumyabubun
2 Replies

8. Shell Programming and Scripting

Print sum and relative value of the sum

Hi i data looks like this: student 1 Subject1 45 55 Subject2 44 55 Subject3 33 44 // student 2 Subject1 45 55 Subject2 44 55 Subject3 33 44 i would like to sum $2, $3 (marks) and divide each entry in $2 and $3 with their respective sums and print for each student as $4 and... (2 Replies)
Discussion started by: saint2006
2 Replies

9. UNIX for Dummies Questions & Answers

sum of column with condition

1 IT 50 2 IT 40 3 Finance 200 4 MP 30 5 MP 10 6 HQ 30 how to use awk to make it display it like this IT 90 MP 40 HQ 30 Finance 200 (1 Reply)
Discussion started by: minwei86
1 Replies

10. Shell Programming and Scripting

sum

Hello everyone I need to write a script that sums numbers passed to it as arguments on the command line and displays the results. I must use a for loop and then rewrite it using a while loop. It would have to output something like 10+20+30=60 this is what I have so far fafountain@hfc:~$ vi sum... (1 Reply)
Discussion started by: Blinky85
1 Replies
Login or Register to Ask a Question