transpose based on condition

Shell Programming and Scripting
1

Hi,

I have the oracle table coulns in an order like
date, state1, state2....state9
and i need to prepare data from the script output for loading in to this table

The script is

#!/bin/ksh
/usr/xpg4/bin/awk -F"-"  '{print $2,$4}' /aemu/ErrorLogs/data/MissingCGIcount.txt | /usr/xpg4/bin/awk -F" " '{print $1,$3}' > /aemu/
ErrorLogs/data/MissingCGIcount_mnc.txt 
sleep 3
/usr/xpg4/bin/awk -F" " '{arr[$1]+=$2} END {for (i in arr) {print i,arr}}' /aemu/ErrorLogs/data/MissingCGIcount_mnc.txt

and the output of the above script is

I need to prepare a text file for loading the above result in to the table based on the following condition

if col1 value is 64, corresponding col2 value is for state1 column in table
if col1 value is 80, corresponding col2 value is for state2 column in table
if col1 value is 72, corresponding col2 value is for state3 column in table
if col1 value is 71, corresponding col2 value is for state4 column in table
if col1 value is 73, corresponding col2 value is for state5 column in table
if col1 value in (38,74,75,76,77,81) corresponding sum of col2 value is for state6 column in table
if col1 value in (57,58,66) corresponding sum of col2 value is for state7 column in table
if col1 value in (34,51,53,54,55,59,62) corresponding sum of col2 value is for state8 column in table
if col1 value in (68,69) corresponding sum of col2 value is for state9 column in table

I need the data like the below format

Note it should add 0 for non existing values

With thanks
Aemunathan

---------- Post updated at 03:40 PM ---------- Previous update was at 01:31 PM ----------

HI

I have carried out by some round about methods ....since am an learner...

#!/bin/ksh
/usr/xpg4/bin/awk -F"-"  '{print $2,$4}' /aemu/ErrorLogs/data/MissingCGIcount.txt | /usr/xpg4/bin/awk -F" " '{print $1,$3}' > /aemu/
ErrorLogs/data/MissingCGIcount_mnc.txt 
sleep 3

/usr/xpg4/bin/awk -F" " '{arr[$1]+=$2} END {for (i in arr) {print i,arr}}' /aemu/ErrorLogs/data/MissingCGIcount_mnc.txt > /aemu/E
rrorLogs/data/MissingCGIcount_mncwise.txt 
sleep 5

CHN=$(/usr/xpg4/bin/awk -F" " '$1=="64"  {print $2} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
TN=$(/usr/xpg4/bin/awk -F" " '$1=="80"  {print $2} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
KER=$(/usr/xpg4/bin/awk -F" " '$1=="72"  {print $2} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
KTK=$(/usr/xpg4/bin/awk -F" " '$1=="71"  {print $2} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
AP=$(/usr/xpg4/bin/awk -F" " '$1=="73"  {print $2} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
EZ=$(/usr/xpg4/bin/awk -F" " '$1=="38" || $1=="74" || $1=="75" || $1=="76" || $1=="77" || $1=="81"  { sum+=$2 } END { print sum} ' /
aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
WZ=$(/usr/xpg4/bin/awk -F" " '$1=="57" || $1=="58" || $1=="66"  { sum+=$2 } END { print sum} ' /aemu/ErrorLogs/data/MissingCGIcount_
mncwise.txt)
NZ=$(/usr/xpg4/bin/awk -F" " '$1=="34" || $1=="51" || $1=="53" || $1=="54" || $1=="55" || $1=="59" || $1=="62"{ sum+=$2 } END { prin
t sum} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
MTNL=$(/usr/xpg4/bin/awk -F" " '$1=="68" || $1=="69" { sum+=$2 } END { print sum} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt
)

if [ $CHN ==  ]
then
CHN=0
fi
if [ $TN ==  ]
then
TN=0
fi
if [ $KER ==  ]
then
KER=0
fi
if [ $KTK ==  ]
then
KTK=0
fi
if [ $AP ==  ]
then
AP=0
fi
if [ $EZ ==  ]
then
EZ=0
fi
if [ $WZ ==  ]
then
WZ=0
fi
if [ $NZ ==  ]
then
NZ=0
fi
if [ $MTNL ==  ]
then
MTNL=0
fi
echo $CHN $TN $KER $KTK $AP $EZ $WZ $NZ $MTNL
exit

but the thing is am getting some errors as well as the correct output.

What would be the problem

---------- Post updated at 04:42 PM ---------- Previous update was at 03:40 PM ----------

Hi

Found the answer for that error as well ffrom this forum itself...
tried this way in all if statements , got the answer

if [ -z "$AP" ]
then
AP=0
ap=0
fi
2

Comparing "something to nothing" is not going to work :wink: what are $CHN et al. supposed to equal?

3

Wow , what's that ? 3 time awk ? somethig like:

awk -F"-"  '{print $2,$4}' file | awk -F" " '{print $1,$3}' | awk -F" " '{arr[$1]+=$2} END {for (i in arr) {print i,arr}}'

Your logic using awk only once.

awk -F"[- ]" '{arr[$2]+=$NF} END {for (i in arr) {print i,arr}}' file
4

cool...its nice to have in one awk...

5

You can try this all-in-one awk solution :rolleyes:

# /usr/xpg4/bin/awk '
BEGIN{FS="[- ]";ORS=" "}                        # Set field separator and other record separator
    $2==64{_[1]+=$NF}                           # if col1 value is 64, corresponding col2 value is for state1 column in table
    $2==80{_[2]+=$NF}                           # if col1 value is 80, corresponding col2 value is for state2 column in table
    $2==72{_[3]+=$NF}                           # if col1 value is 72, corresponding col2 value is for state3 column in table
    $2==71{_[4]+=$NF}                           # if col1 value is 71, corresponding col2 value is for state4 column in table
    $2==73{_[5]+=$NF}                           # if col1 value is 73, corresponding col2 value is for state5 column in table
    $2~"38\|7[4567]\|81"{_[6]+=$NF}             # if col1 value in (38,74,75,76,77,81) corresponding sum of col2 value is for state6 column in table
    $2~"5[78]\|66"{_[7]+=$NF}                   # if col1 value in (57,58,66) corresponding sum of col2 value is for state7 column in table
    $2~"38\|7[4567]\|81"{_[8]+=$NF}             # if col1 value in (34,51,53,54,55,59,62) corresponding sum of col2 value is for state8 column in table
    $2~"6[89]"{_[9]+=$NF}                       # if col1 value in (68,69) corresponding sum of col2 value is for state9 column in table
END{
        for(i=0;++i<10;) print (_)?_:0    # for each member of _ array print value or add 0 if no value set
   }
' /aemu/ErrorLogs/data/MissingCGIcount.txt
6

Nice result...very effective coding...Thanks