0

I'm working in a unique validation framework that validates data. For each validation job there is a SQL job with an accompanying KSH job (kornshell). The SQL queries something in the database, and the KSH needs to query the data file using the same logic as the DB (SQL).

My current SQL statement queries the sum of an aggregate column by a date that I hard coded, and gives me a value. How can I replicate that in the below KSH file?

Currently, this is how my KSH code is looking like:

cat /textfile.txt | > ${OUTPUT_FILE}

So, what this KSH needs to do is give me the sum of a specific column for a specified date. There will be multiple records for dates.

Example Data:

Date  | Money
--------------
1/1/15 : $15
2/2/15 : $14
3/3/16 : $25
etc... : etc...

Expected Output: Sum(Money) from 1/1/15 - 1/31/15.

3
  • You should provide sample data and expected output in your question. – Stephen Harris Nov 19 '18 at 23:39
  • @StephenHarris done. Please check updated postt – Nathan Nov 19 '18 at 23:45
  • ksh and other scripting languages aren't always the best solution; there's already an awk solution posted. does the solution have to be in ksh, for some reason? – Jeff Schaller Nov 20 '18 at 0:00
0

The exact syntax will depend on the exact input. If the value field always starts <space><dollar> then we can use an awk statement:

awk -F: '$2 ~ /^ \$/ { a[$1]+=substr($2,3)} END {for (b in a) { print b"$"a[b]}}'

eg

awk -F: '$2 ~ /^ \$/ { a[$1]+=substr($2,3)} END {for (b in a) { print b"$"a[b]}}' testfile.txt
3/3/16 $25
2/2/15 $14
1/1/15 $15

Note: if the seperator character is really a | and not a : then you'll need to do awk -F'|' ....

The way this works is:

$2 ~ /^ \$/ -- for each line where the second field begins <space><dollar>

{ a[$1]+=substr($2,3)} -- Add the content of the second field (stripping off the first two characters) to an array indexed by the date

END {for (b in a) { print b"$"a[b]}}' -- when the file has ended, go through all the dates we found, and print the total.

So, basically, the a array is indexed by date and contains the sum of values seen for that date.

1
  • You may want to remove the day from the date string before used as an index, as the requirement obviously is to sum per month. – RudiC Nov 20 '18 at 13:00
0

Well if you need it in ksh that sample may help you :

Data file I've used :

Date  | Money
--------------
1/1/15 : $15
2/2/15 : $14
3/3/16 : $25
1/2/15 : $10
2/1/15 : $35
3/2/15 : $7

Code :

#!/bin/ksh

typeset -A SUM_ARRAY

IN_FILE="$1"

if [[ -z $IN_FILE ]]; then
        print "Usage : script.sh {input filename}"
        exit
fi


while read line;
do
        temp_array=($line)
        if [[ ! ${temp_array[0]} =~ ^(-*)$ && ${temp_array[0]} != "Date" ]]; then
                idx=$(date -d ${temp_array[0]} +"%Y%m")
                SUM_ARRAY[$idx]=$((${SUM_ARRAY[$idx]}+${temp_array[2]#\$*}))
        fi
done < $IN_FILE

print "YearMonth\tSum"

for idx in ${!SUM_ARRAY[@]}; do
        print "$idx\t\t${SUM_ARRAY[$idx]}"
done

Result :

YearMonth       Sum
201501          25
201502          49
201503          7
201603          25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.