0 Replies Latest reply: Feb 24, 2011 10:23 AM by Joe Kirwan RSS

    Script:  Add New_Field showing max in Old_Field1 for groups of data in Old_Field2

    Joe Kirwan

      I have existing fields as shown in left hand table below. (The objects below are Table Box objects - the one on the right shows the effect of my script on the one on the left.)

      These fields are in table "Trans2" in my application. I am trying to add MAX_DEL to this table.

      I have tried a number of load statements with various joins, all based around the script below, which at least seems to calculating the max value for each field correctly.

      The problem is that it is creating a new Trans2-1 table which contains a row showing MAX_DEL for each SORDREF.

      However in Trans2 there are multiple rows for some SORDREFs.

      I need a script that will add the MAX_DEL field to each line in my Trans2 table showing the MAX_DEL per order for each row of the SORDREF, not create a new table as the script below does.

      LOAD SORDREF,

      max(DEL_COUNT) as MAX_DEL

      Resident Trans2

      Group by SORDREF;