Compute a field based on value from another table

[#1]

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;

[#2 · Accepted solution]

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

[#3]

I would prefer a more generic method where the join statement supported a 'where' part where fields from both tables could be tested. But I guess that is not possible.

For now, your solution is sufficient.Thanks