5 Replies Latest reply: Jun 4, 2013 9:09 AM by Daniel Mattsson RSS

    Fill out increments between numbers

      Hi,
      I wish to generate a table which fills out integers, based on another table. I want all the integers between Min and max of received (with the same Name and Reference) to be generated.
      (Edit - added for clarity):

      The rule could be formulated as following:

      For each Name and Reference:

      Find its smallest and largest "Received" value.

      Fill out all integers between these values in the Received column.

      Example of first table:
      NameReceivedReference
      a47
      a67
      a38
      a58
      b27
      b37
      b49
      b59
      b89
      What I want the new table to look like (new lines with blue text):
      NameReceivedReference
      a47
      a57
      a67
      a38
      a48
      a58
      b27
      b37
      b49
      b59
      b69
      b79
      b89
      Inline script:
      LOAD
       * INLINE [
          Name, Received, Reference
          a, 4, 7
          a, 6, 7
          a, 3, 8
          a, 5, 8
          b, 2, 7
          b, 3, 7
          b, 4, 9
          b, 5, 9
          b, 8, 9
      ]; 
       
      
      Any help is much appreciated!
        • Re: Fill out increments between numbers
          Jonathan Brough

          I've not quite understood what rules you are using for setting the Refernce on these new records, but you could:

          1. use a cartesian join to multipy the records to return all combinations of fields Name and Received

          2. join on your reference field.

          3. join on the min and max of your Received field

          4. recreate your table with Received between the min and max

           

          Something like this:

           

          OrigTab:

          LOAD
          * INLINE [
              Name, Received, Reference
              a, 4, 7
              a, 6, 7
              a, 3, 8
              a, 5, 8
              b, 2, 7
              b, 3, 7
              b, 4, 9
              b, 5, 9
              b, 8, 9
          ];

           

          // 1. use a cartesian join to multipy the records to return all combinations of fields Name and Received

          NewTab:

          LOAD Name

          RESIDENT OrigTab;

           

          LEFT JOIN (NewTab)

          LOAD Received

          RESIDENT OrigTab;

           

          // 2. join on your reference field, grouping bu Name and Received

          LEFT JOIN (TempTab)

          LOAD Name,

               Received,

               only(Reference) AS Reference

          RESIDENT OrigTab

          GROUP BY Name, Received;

           

          // 3. join on the min and max of your Received field

          LEFT JOIN (TempTab)

          LOAD Name,

               min(Received) AS MinReceived,

               max(Received) AS MaxReceived

          RESIDENT OrigTab

          GROUP BY Name;

           

          // 4. recreate your table with Received between the min and max

          NewTab:

          NOCONCATENATE LOAD

               Name,

               Received,

               Reference

          RESIDENT TempTab

          WHERE Received >= MinReceived

          AND Received <= MaxReceived;

           

          DROP TABLES OrigTab, TempTab;

           

          Jonathan

            • Re: Fill out increments between numbers

              Thanks a lot!

               

              I didn't know you could use the While function in such way!

               

              In case any one else needs it, the final, working script looks like this:

               

               

               

              OrigTab:

               

               

              LOAD

              *
              INLINE [
                  Name, Received, Reference
                  a, 4, 7
                  a, 6, 7
                  a, 3, 8
                  a, 5, 8
                  b, 2, 7
                  b, 3, 7
                  b, 4, 9
                  b, 5, 9
                  b, 8, 9
              ]
              ;

              TempTab:
              LOAD
              Name,
              Max(Received) As MaxReceived,
              Min(Received) As MinReceived,
              Reference
              Resident OrigTab
              Group by Name, Reference;

              DROP TABLE OrigTab;

              TempTab2:
              LOAD

              Name,
              MinReceived + IterNo() - 1 As Received,
              Reference
              Resident TempTab
              While IterNo() <= MaxReceived - MinReceived + 1 ;

              DROP TABLE TempTab;