problem with mapping two files with KSH

[amonamon]

I am trying to write script that would do this scenario..in ksh

and FS is ; in both files.

general.txt:

323232 ; 10 ; 1 ; N
777777 ; 10 ; 2 ; N
989898 ; 10 ; 4 ; N


new file comes with values:

newfile.txt

323232 ; 10 ; 1
777777 ; 20 ; 1
989898 ; 40 ; 1

then general.txt should look like:


***** general file: ****
id ; sum ; number-of-sums ; yesno

323232 ; 20 ; 2 ; N
777777 ; 30 ; 3 ; Y
989898 ; 50 ; 5 ; Y

here comes more scenarios on new files that comes and should change general.txt files
___________________________________________

new file comes with values:

new file:
323232 ; 10 ; 1
777777 ; 10 ; 1
989898 ; 10 ; 1
656565 ; 10 ; 1

***** general file: ****
id ; sum ; number-of-sums ; yesno

323232 ; 30 ; 3 ; Y
777777 ; 40 ; 4 ; Y
989898 ; 60 ; 6 ; Y
656565 ; 10 ; 1 ; N

___________________________________________

new file comes with values:

new file:
323232 ; 10KM ; 1
777777 ; 10KM ; 1
989898 ; 20KM ; 1
909099 ; 20KM ; 1

***** general file: ****
id ; sum ; number-of-sums ; yesno

323232 ; 40 ; 4 ; Y
777777 ; 50 ; 5 ; Y
989898 ; 80 ; 7 ; Y
656565 ; 10 ; 1 ; N
909099 ; 20 ; 1 ; N


___________________________________________

new file comes with values:

new file:
656565 ; 10 ; 1
380000 ; 10 ; 1
656565 ; 50 ; 1
909099 ; 20 ; 1

***** general file: ****

id ; sum ; number-of-sums ; yesno
323232 ; 40 ; 4 ; Y
777777 ; 50 ; 5 ; Y
989898 ; 80 ; 7 ; Y
656565 ; 70 ; 3 ; Y
909099 ; 40 ; 2 ; Y
380000 ; 10 ; 1 ; N

[Hein van den Heuvel · Accepted solution]

- Does the yesno column indicate whether this was a 'new' value'

- Is 'yesno' always N of number-of-sums is 1 and Y if more than 1?

- Is the "N" for 323232 in the second general file a bad example?
323232 ; 20 ; 2 ; N

- Do 'new' values always have to be added to the end? If now, what it the sort order? Text or numbers?

- Is that 'KM' an option text in the sum column for the new file? Any other surprises?

- Is the last column in new file always 1?

- Should number-of-sums always be just incremented by one, or the last column from new added.


perl example below, giving sorted output.
use as : perl test.pl general new

Hein.

--------- test.pl --------------
use strict;
my $old = shift or die "please provide general and new files";
my $new = shift or die "please provide new file";
my (%sum, %number_of_sums, %yesno);

open OLD, "<$old" or die "could not open general file $old";
while () {
next unless /^\s*\d+/;
chomp;
my ($id, $s, $n, $yn) = split /\s*;\s*/;
$sum{$id} = $s;
$number_of_sums{$id} = $n;
$yesno{$id} = $yn;
}

open NEW, "<$new" or die "could not open general file $new";
while () {
chomp;
my ($id, $s, $n, $yn) = split /\s*;\s*/;
$yesno{$id} = (exists ($yesno{$id})) ? 'Y' : 'N';
$s =~ s/KM//;
$sum{$id} += $s;
$number_of_sums{$id} += $n;
}

print "id ; sum ; number-of-sums ; yesno\n\n";
foreach (sort keys %yesno) {
print "$_ ; $sum{$_} ; $number_of_sums{$_} ; $yesno{$_}\n";
}

[Dino_4]

Here is a ksh-script:
It will only work, if the new files have just numbers in column 2 and 3.
The filename of the newfile has to be $1.

#!/usr/bin/ksh

GEN_FILE=./general.txt
NEW_FILE=${1}

typeset -i mySUM=0
typeset -i myNOS=0
typeset -i newSUM=0
typeset -i newNOS=0
typeset -i valSUM=0
typeset -i valNOS=0

while read line
do

newID=`echo $line | awk 'BEGIN { FS=" ; " } { print $1 }'`
newSUM=`echo $line | awk 'BEGIN { FS=" ; " } { print $2 }'`
newNOS=`echo $line | awk 'BEGIN { FS=" ; " } { print $3 }'`

grep "${newID}" ${GEN_FILE} > /dev/null 2>&1
if [[ $? -ne 0 ]]
then

echo "${newID} ; ${newSUM} ; ${newNOS} ; N" >> ${GEN_FILE}

else

mySUM=`grep $newID ./general.txt | awk 'BEGIN { FS="; " } { print $2 }'`
myNOS=`grep $newID ./general.txt | awk 'BEGIN { FS="; " } { print $3 }'`

let valSUM=${mySUM}+${newSUM}
let valNOS=${myNOS}+${newNOS}

if [[ ${valNOS} > 2 ]]
then
newYN="Y"
else
newYN="N"
fi

grep -v ${newID} ${GEN_FILE} > ${GEN_FILE}_new

echo "${newID} ; ${valSUM} ; ${valNOS} ; ${newYN}" >> ${GEN_FILE}_new


mv ${GEN_FILE}_new ${GEN_FILE}

fi

done < ${NEW_FILE}

[Hein van den Heuvel]

That could work Dino, as long as you don't mind the new lines earliy in the general files. And as long as there was not thousands of lines as the files will eb read thousands of times. And are you sure you get the old lines from general which did not have a new line?

Detail comment>>> mySUM=`grep $newID ./general.txt | awk 'BEGIN { FS="; " } { print $2 }'`

Why not let awk also do the match?
Like:

mySUM=$(awk -F';' '/'$newID'/{ print $2 }' $GEN_FILE)

Hein

---- perl variant leaving old general order in place ----

use strict;
my $old = shift or die "please provide general and new files";
my $new = shift or die "please provide new file";
my (%sum, %number_of_sums);
my ($id, $s, $n, $yn);

open NEW, "<$new" or die "could not open general file $new";
while () {
chomp;
($id, $s, $n) = split /\s*;\s*/;
$s =~ s/KM//;
$sum{$id} += $s;
$number_of_sums{$id} += $n;
}

open OLD, "<$old" or die "could not open general file $old";
while () {
if (/^\s*(\d+)\s*;/) {
if (exists ($sum{$1})) {
($id, $s, $n, $yn) = split /\s*;\s*/;
$s += $sum{$id};
$n += $number_of_sums{$id};
$yn = "Y";
delete $sum{$id};
}
$_ = "$id ; $s ; $n ; $yn\n";
}
print;
}
foreach $id (keys %sum) {
print "$id ; $sum{$id} ; $number_of_sums{$id} ; N\n";
}

[Dino_4]

Hi Hein,

you are right.
I didn't think of sorting because once it has a certain size you have to grep for the needed information anyway, I guess.

Btw: Let AWK do the work of mathing is very neat. Didn't thought about that. :-(

[amonamon]

Yes thanks but I have no clue about perl..and I do not have perl copiler..I forgot to mention that.

here are some answers to your Q.

Q Does the yesno column indicate whether this was a 'new' value'

A if second field is greater then 30 then 4th field should be Y.

Q - Is 'yesno' always N of number-of-sums is 1 and Y if more than 1?

number of sums is not thet much releveant field..it can be number od sums 2 and YN flag can be N. for example if value in general.txt is 10 and value in newfile.txt is 10 (10+10 = 20 that is lower then 30 so flag is N)

Q
- Is the "N" for 323232 in the second general file a bad example?
323232 ; 20 ; 2 ; N

No it is not bad example as U can see in seconf field it stands 20 (lower then 30)

Q - Do 'new' values always have to be added to the end? If now, what it the sort order? Text or numbers?

new values are added thru new txt file and two files are mapping as I explaind better in post #1.

Q
- Is that 'KM' an option text in the sum column for the new file? Any other surprises?

forgot the KM it is misstake it should not be there..

Q - Is the last column in new file always 1?

Yes it is always 1..(I hope so..:()

Q - Should number-of-sums always be just incremented by one, or the last column from new added.

well it should be incremented by one if value in newfile last filed is one

if new file is:
new file:
656565 ; 10 ; 1
380000 ; 10 ; 1
656565 ; 50 ; 1
909099 ; 20 ; 1

then it should be incremented by 1.

thanks and I would appreciate any solution in KSH.

regards

[amonamon]

thanks dino..I think this is what I wanted..I works..

regards.
thanks everyone!!!