topic Compute a field based on value from another table in QlikView

Compute a field based on value from another table

Fri, 30 Dec 2011 11:11:31 GMT

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

Fri, 30 Dec 2011 11:52:44 GMT

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

Re: Compute a field based on value from another table

Fri, 30 Dec 2011 12:00:57 GMT

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