Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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;

1 Solution

Accepted Solutions
Not applicable

Compute a field based on value from another table

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

2 Replies
Not applicable

Compute a field based on value from another table

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

Not applicable

Re: Compute a field based on value from another table

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