2 Replies Latest reply: Dec 30, 2011 7:01 AM by Rune Christensen RSS

    Compute a field based on value from another table

      This script doesn't work (Field not found: <level>):

      **********************

      Table1:

      LOAD * INLINE [

          low, high, levelName

          1, 3, warning

          4, 7, error

          8, 9, fatal

      ];

       

      Table2:

      LOAD * INLINE [

          num, level

          1, 3

          2, 6

          3, 9

      ];

       

      Join(Table2)

      LOAD levelName resident Table1

      where (level >= low and level <= high);

      **********************

      What I wanted is to produce the table

      num, levelName

      1, warning

      2, error

      3, fatal

       

       

      Is this only possible by first producing a (far too huge) table doing a cross join on Table1 and Table2 followed by filtering?

      E.g:

      Table1:

      LOAD * INLINE [

          low, high, levelName

          1, 3, warning

          4, 7, error

          8, 9, fatal

      ];

       

      Table2:

      LOAD * INLINE [

          num, level

          1, 3

          2, 6

          3, 9

      ];

       

      Join(Table2)

      LOAD * resident Table1;     // Result-table is too big

       

      Table3:

      load num, levelName resident Table2

      where (level >= low and level <= high);

       

      drop table Table1;

      drop table Table2;

        • Compute a field based on value from another table
          Ashutosh Paliwal

          Hi Rune,

                     You can use intervalmatch here. I have made a sample script for you. Modify it according to your need.

           

           

          Table1:

          LOAD * INLINE [

              low, high, levelName

              1, 3, warning

              4, 7, error

              8, 9, fatal

          ];

          Table2:

          LOAD * INLINE [

              num, level

              1, 3

              2, 6

              3, 9

          ];

          Table3:

          IntervalMatch(level)  LOAD

          low,

          high

          Resident Table1;

          Inner Join(Table3) LOAD

          *,

          0 as junk

          Resident Table1;

          DROP Table Table1;

          Left Join(Table3) LOAD

          level,

          num,

          0 as junk1

          Resident Table2;

          DROP Table Table2;

          DROP Fields low,high,junk,junk1,level;

           

          ***********

           

          Regards,

          Ashutosh