Link a Variable Number of Records With Qedit

Vic Ferrara (from The Wine Enthusiast) called us with an interesting challenge. He had a file containing EDP-NOs and keywords, with multiple records (up to 12) per EDP-NO value.

He needed to combine the records so that each EDP-NO value had only 1 record, with all keywords for that EDP-NO shown on that record....

In other words, he had to change:

  edp1 keyword1 keyword2
  edp1 keyword3 keyword4 keyword5
  edp1 keyword6
  edp2 keyword1 keyword2
  edp2 keyword3 keyword4
  edp3 keyword1 keyword2
  edp4 keyword1 keyword2
  edp4 keyword3 keyword4
  ...etc

...into:

  edp1 keyword1 keyword2 keyword3 keyword4 keyword5 keyword6
  edp2 keyword1 keyword2 keyword3 keyword4
  edp3 keyword1 keyword2
  edp4 keyword1 keyword2 keyword3 keyword4

Our first inclination was to use Suprtool and Suprlink to split the input file into 12 separate files (one for each "occurrence" of the EDP-NO), and then link them back into 1 "composite" file. But this would require 23 Suprtool tasks to separate up to 12 "duplicates" per EDP-NO.

So we turned to Qedit, and its "Glue" command.....

Firstly, we needed a way to test whether a record had the same EDP-NO as the previous one. Luckily Glenn Cole came up with a way to do this some years ago, via his QSETVAR command file:

QSETVAR.CMD
-----------
parm varname
input !varname
setvar !varname rtrim(!varname)

This sets a specified variable to a prompted value. It can be used in Qedit to store the value of a line as follows:

      /holdq *                {copy current line to HOLD file}
      /qsetvar  curr < hold

The variable "curr" will be set to the value of the line "held". This allows us to do comparisons in our JCL, if we store the value of the previous record each time we move forward in the file:

     setvar  prev,curr
     /l *+1
     /holdq *
     /qsetvar  curr < hold
     if cur = prev then....

So now we can establish which are "duplicate" lines, so we can mark the lines to be "glued" by adding a tilde to the end.

Warning: The final glued lines can not have more than 8,172 characters. This is the absolute maximum in Qedit. If lines may exceed the maximum length, results are unpredictable. In this situation, another solution has to be designed.

OK, we're set! Here's the final command file we created:

      /set window (up)        {make searches non-case-sensitive}
      purge edpout
      setjcw cierror = 0
      setvar prev,"#"
      setvar curr," "
      continue
      /set right 4            {only look at the EDP-NO}
      /list first
      while cierror=0 do
        /holdq *
        /qsetvar  curr < hold
        if prev = curr  then  {current line same as previous!}
          /set right          {widen "window" to whole record}
          /appq " ~" *-1      {append a tilde to previous line}
          /set right 4        {narrow window back to EDP-NO only}
          /cha 1/4 "    " *+1 {change EDP-NO to blanks in duplicate record}
        endif
        setvar  prev,curr
        continue
        /lq*+1                {move forward 1 line}
      endwhile
      /set right              {set right margin back to full record length}
      /holdq @                {copy all the lines to the Qedit Hold file}
      /shut
      /new edpout,wide        {create a wide-jumbo workfile}
      /set len 8172
      /aq *=hold
      /glue "~"@              {glue lines that end with a tilde}
      while qeditcount > 0 do {while there are still tildes, do...}
        /cha "(.+) ~(.+)"(regexp) "\1 \2"@   {remove *embedded* tildes from glued lines}
        /glue "~"@            {glue lines that still have a tilde (at end of line)}
      endwhile
      /cq "~"" last           {remove tilde from last line}

To use the command, you simply need to text the file which contains the line to be combined and execute the command file. Let's assume the data file is called edpdata, the command file is called edpglue and the final results saved in a text file called edplist, you would do the following:

    /Text edpdata
    /edpglue
    /Keep edplist
    /Shut
    /Purge edpout

The last 2 steps (Shut and Purge) are required to get rid of the temporary workfile created by edpglue.