Hi Everyone,
I am confused with the output of the input file and I am using below command in script to get the expected output.
Also I want to add another condition using logical AND (&&) in place of $2=="L"{$4=0-$4} as $2=="L" && $3=="L" {$4=0-$4} but I am getting some awk error.
Can someone please advise on this please?

 
cat $FILENAME |grep "^PL"|tr -d '\015'|sort -k1,2|nawk -F'|' 'BEGIN {OFS="~"}$2=="L"{$4=0-$4};$6=="COM"?$6="SHS":$6="FMT";{arr[$1"~"$5"~"$6]+=$4} END {for (i in arr) {print i,arr[i]}}'>$TEMPFILE
Input File 
============
ISIN|BL|STATUS|QTY|SNAME|CLASS
PLKGHM000017|B|L|85000|KGHM|COM
PLPKO0000016|B|L|310000|PKO S.A.|COM
|B|L|0||
PLKGHM000017|L|L|35000|KGHM|COM
PL0000101937|B|L|100000|DS1110|GOV
|L|L|0||
PLKGHM000017|L|L|40000|KGHM|COM
PLPKO0000016|L|L|290000|PKO S.A.|COM
Present Output
==============
PL0000101937~B~L~100000~DS1110~FMT
PLKGHM000017~B~L~85000~KGHM~SHS
PLKGHM000017~L~L~-35000~KGHM~SHS
PLKGHM000017~L~L~-40000~KGHM~SHS
PLPKO0000016~B~L~310000~PKO S.A.~SHS
PLPKO0000016~L~L~-290000~PKO S.A.~SHS
PLPKO0000016~PKO S.A.~SHS~20000
PL0000101937~DS1110~FMT~100000
PLKGHM000017~KGHM~SHS~10000
Expected Output
==============
PLPKO0000016~PKO S.A.~SHS~20000
PL0000101937~DS1110~FMT~100000
PLKGHM000017~KGHM~SHS~10000

can you provide a sample of the expected output here? From your post, it is not clear what you are trying to achieve.


cheers,
Devaraj Takhellambam

You really don't need 'cat', 'grep' etc.... - awk can do it all itself.

nawk -f geh.awk myFile

geh.awk:

BEGIN {
  FS="|"
  OFS="~"
}
!/^PL/ { next }
$2=="L" && $3=="L" {$4=0-$4}
{
  $6=($6=="COM")?"SHS":"FMT"
  arr[$1 OFS $5 OFS $6]+=$4
}
END {
  for (i in arr)
    print i,arr[i]
}

Thanks Vgersh , I have correct the code as per your advise.

I am getting expected output if i use awk operation twice i.e. by storing the output before array operation
and on new file if i use array operation I am getting correct output.

But as I was using awk twice , I thought of combining it. Do we something like pipe in awk ?


So it will be like first get this file..

PL0000101937~B~L~100000~DS1110~FMT
PLKGHM000017~B~L~85000~KGHM~SHS
PLKGHM000017~L~L~-35000~KGHM~SHS
PLKGHM000017~L~L~-40000~KGHM~SHS
PLPKO0000016~B~L~310000~PKO S.A.~SHS
PLPKO0000016~L~L~-290000~PKO S.A.~SHS

I don't quite follow what you're doing - sorry.
Could you post the steps and the modified code, please?

vgersh,

Step 1 :Initial File Conversion for basic calculation

cat INPUTFILE |tr -d '\015'|sort -k1,2|nawk -F'|' 
'BEGIN {OFS="~"}!/^PL/{next}$2=="L" && $3=="L"
{$4=0-$4};$6=="COM"?$6="SHS":$6="FMT"{print $0}'>TEMPFILE

 awk -F'~' '{OFS="~"}{arr[$1OFS$5OFS$6]+=$4} END 
  {for (i in arr) {print i,arr[i]}}'<TEMPFILE>EXPECTEDFILE