awk to get lowest & store value from another file...

Shell Programming and Scripting
1

I have two files. One small stores and one with large stores like below...

Small-stores.txt

Store	Code	Excess Stock
K76	23812	17
K50	23812	6
K16	23812	4
K22	23812	3
K69	23812	3
K76	23813	10
K50	23813	7
K16	23813	6
K69	23813	4
K22	23813	3
K69	23814	10
K76	23814	6
K16	23814	4
K50	23814	4
K22	23814	3
K76	30002	4
K16	30002	2
K69	30002	2
K22	30002	1

Large-stores.txt

Store	Code	Excess Stock
K15	23812	4
K47	23812	4
K48	23812	13
K53	23812	3
k03	23812	2
K17	23813	19
k03	23813	7
K46	23813	7
K78	23813	17
K24	23813	4
K30	23814	6
K60	23814	6
k90	23814	5
K17	23814	4
K44	23814	4
K26	30002	2
k03	30002	-5
K05	30002	-6
K09	30002	-5
K13	30002	-2
K14	30002	-2
K15	30002	20
K71	30002	-1
K78	30002	-1
K87	30002	-3
k90	30002	-1

what i want to do is that, i want to transfer the excess stocks i have from small stores to large stores.

expected output...

Store	Code	Excess	Transfer-To Transfer-qty
K76	23812	17	k03	8
K50	23812	6	k53	6
K16	23812	4	k47	4
K22	23812	3	k15	3
K69	23812	3	null	null
K76	23813	10	k24	6
K50	23813	7	k03	3
K16	23813	6	k46	3
K69	23813	4	null	null
K22	23813	3	null	null
K69	23814	10	k44	6
K76	23814	6	k17	6
K16	23814	4	k90	4
K50	23814	4	k60	4
K22	23814	3	k30	3
K76	30002	4	k90	4
K16	30002	2	k87	2
K69	30002	2	k78	2
K22	30002	1	k71	1

To make it clear...

  1. I want to get the biggest excess stock from small-stores.txt file and transfer it to smallest excess stocks in large-store.txt file. I can go up to positive 10 in excess stock in large-stores.txt file and minimize the excess stocks in small-stores.txt file to zero.
2

Hi, give this a try:

awk '
  {
    while((p || B[2]<$2 || B[3]>10) && (getline n<f)>0) {
      split(n,B)
      p=0
    }
    t=q="null"
    if(B[2]==$2 && B[3]<10) {
      t=B[1]
      q=(10-B[3]<$3 ? 10-B[3] : $3)
      p=1
    } 
    print $0, t, q
  }
' OFS='\t' f=<(sort -k2,2n -k3n  Small-stores.txt) <(sort -k2,2 -k3rn Large-stores.txt)

---
This is using process substitution in bash/ksh93/zsh. If the files are already in the proper sorted order, you can just use:

' OFS='\t' f=Small-stores.txt Large-stores.txt
1 Like