I have 2 files with sample data enclosed.

1. GL (already sorted in ascending order by Gl number)
   Gl number*glname*Yearopening balance
   1000Interest Income*2006100.00
   1005Rental Income*2006*0.00
   ...

2. Transactions (file is not sorted on GL number or any other field)
   Branch*YearGl numberTrans IDPosting Date*CurrencyAmount
   BR1*2006*1000*00002*01/03/2006*USD36.65
   BR5*2006*1005*00001*01/04/2006*USD2000.00
   BR1*2006*1000*00005*01/14/2006*USD*150.00
   ...

How will I obtain a Gl numberwise control break report combining data from these 2 files.
--------------------------------------------------
Year: 2006 GL number: 1000 GL name : Interest Income
Opening Balance: 100

Posting date Currency TransID Amount
01/03/2006 USD 00002 36.65
01/14/2006 USD 00005 150.00

Total 286.65

Year: 2006 GL number: 1005 GL name : Rental Income
Opening Balance : 0.00
Posting date Currency TransID Amount
01/04/2006 USD 00001 2000.00

Total 2000.00

...

I would appreciate any point to resolve this. It is not a h/w assignment. Thanks in advance.

#! /usr/bin/ksh

rm result1
while read line
do
gl=`echo $line | cut -d\* -f1`
obal=`echo $line | cut -d\* -f4`
echo "Opening Balance : $obal" >> result1
cat file2 |awk -F* '
{if($3 == g){totbal = totbal + $7}
{print $5 " " $6" " g " "$7}
}END{print "Total Balance : " totbal"\n" }' g="$gl" totbal="$obal" >>result1
done < file1

Aju,

Thank you very much. The output is attached based on your script. But I see just one problem. I require an output only when there is a matching GL number in the transactions file. If not that GL number should be skipped.

$ more file1
1000Interest Income*2006100.00
1005Rental Income*20060.00

$ more file2
BR1*2006*1000*00002*01/03/2006*USD36.65
BR5*2006*1005*00001*01/04/2006*USD2000.00
BR1*2006*1000*00005*01/14/2006*USD*150.00

$ more result1
Opening Balance : 100.00
01/03/2006 USD 1000 36.65
01/04/2006 USD 1000 2000.00 (should not be here as it is GL 1005, but total is correct)
01/14/2006 USD 1000 150.00
Total Balance : 286.65 (total is correct)

Opening Balance : 0.00
01/03/2006 USD 1005 36.65 (should not be there as it is GL 1000)
01/04/2006 USD 1005 2000.00
01/14/2006 USD 1005 150.00 (should not be there as it is GL 1000)
Total Balance : 2000 (total is correct)

How would I modify the script. Thanks.

#! /usr/bin/ksh

rm result1
while read line
do
gl=`echo $line | cut -d\* -f1`
obal=`echo $line | cut -d\* -f4`
#echo "Opening Balance : $obal" >> result1
cat file2 |awk -F* 'BEGIN{opbl=0}
{if($3 == g){
if(opbl == 0) {
print "Opening Balance :" totbal
opbl=1
}
totbal = totbal + $7
{print $5 " " $6" " g " "$7}
}
}END{print "Total Balance : " totbal"\n" }' g="$gl" totbal="$obal" >>result1
done < file1

I am still getting the output in the following manner:

$ more result1
Opening Balance : 100.00
01/03/2006 USD 1000 36.65
01/14/2006 USD 1000 150.00
01/04/2006 USD 1000 2000.00 (should not be printed as the GL is different)
Total Balance : 286.65

Opening Balance : 0.00
01/03/2006 USD 1005 36.65 (should not be printed as the GL is different)
01/14/2006 USD 1005 150.00 (should not be printed as the GL is different)
01/04/2006 USD 1005 2000.00
Total Balance : 2000

It looks like it is not doing a key match or am I doing something wrong.

The following is the script used:
------------------------------
rm result1
while read line
do
gl=`echo $line | cut -d\* -f1`
obal=`echo $line | cut -d\* -f4`
#echo "Opening Balance : $obal" >> result1
cat file2 |awk -F* 'BEGIN{opbl=0}
{if($3 == g){
if(opbl == 0) {
print "Opening Balance :" totbal
opbl=1
}
totbal = totbal + $7
{print $5 " " $6" " g " "$7}
}
}END{print "Total Balance : " totbal"\n" }' g="$gl" totbal="$obal" >>result1
done < file1

nawk -f aug.awk myGL.txt myTRAN.txt

aug.awk:

BEGIN {
  FS="*"
  format="%-15s %-10s %-10s %10s\n"
}

FNR == 1 { next }
FNR == NR {
   glA[$1]=$3
   glA_glname[$1]=$2
   glA_open[$1]=$4
   next
}

{
   str=sprintf(format, $5, $6, $4, $7)
   tranA[$3]= ( $3 in tranA) ? tranA[$3] str : str;
   tranA_total[$3]+=$7
}
END {
  for (i in glA) {
     printf("Year: %s GL number: %s GL name: %s\n", glA, i, glA_glname)
     printf("Opening Balance: %s\n\n", glA_open)

     printf(format, "Posting date", "Currency", "TransID", "Amount")
     printf("%s", tranA)
     printf("\nTotal %.2f\n\n", tranA_total + glA_open)
  }
}

Thank you. As I did not have nawk, I ran the following at the HP UNIX prompt with awk. file1 is GL and file2 is transactions. It gives only the output for GL 1005. The output for GL 1000 is missing. Should I make any change to the code?

command and the output
------------------------
$ awk -f aug.awk file1 file2 > file3
$ more file3
Year: 2006 GL number: 1005 GL name: Rental Income
Opening Balance: 0.00

Posting date Currency TransID Amount
01/04/2006 USD 00001 2000.00

Total 2000.00

run it without redirecting to a file first.

runs just fine under Solaris' 'nawk' and the sample files previously posted. Check your input files

file1 :-
1000Interest Income*2006100.00
1005Rental Income*20060.00

file2:
BR1*2006*1000*00002*01/03/2006*USD36.65
BR5*2006*1005*00001*01/04/2006*USD2000.00
BR1*2006*1000*00005*01/14/2006*USD*150.00

i am using above files as input files and getting following output after the same script

Opening Balance :100.00
01/03/2006 USD 1000 36.65
01/14/2006 USD 1000 150.00
Total Balance : 286.65

Opening Balance :0.00
01/04/2006 USD 1005 2000.00
Total Balance : 2000

If there any problem in input files please prompt me.

---------------
Ajay

prompt: your input files are missing HEADER lines as you had them in the original one.

if you're NOT going to have header lines, then delete line

FNR == 1 { next }

Thanks Ajay and Vgersh! You guys are great!

I have obtained the output now.

$ more result1
Opening Balance :100.00
01/03/2006 USD 1000 36.65
01/14/2006 USD 1000 150.00
Total Balance : 286.65

Opening Balance :0.00
01/04/2006 USD 1005 2000.00
Total Balance : 2000

Many thanks!

I am pasting the output obtained by the awk command. I did not have nawk, so I ran it with awk:

$ awk -f aug.awk file1.vg file2.vg

Year: 2006 GL number: 1000 GL name: Interest Income
Opening Balance: 100.00

Posting date Currency TransID Amount
01/03/2006 USD 00002 36.65
01/14/2006 USD 00005 150.00

Total 286.65

Year: 2006 GL number: 1005 GL name: Rental Income
Opening Balance: 0.00

Posting date Currency TransID Amount
01/04/2006 USD 00001 2000.00

Total 2000.00

Once again thank you to everyone who contributed and I am closing this thread.