0

# Option to implement LOOKUP( ) Functionality in HANA Calculation View

Jul 27, 2017 at 03:34 AM

340

Former Member

Hi All,

I had this requirement to lookup a value from one table to another containing only ranges of value. The approach was to use a graphical calculation view but I can't seem to make it work because it has to have a join between table A and B.

To put a bit of clarity, say we have Table A - TBL_PRINCIPAL_MASTER and Table B - TBL_PRINCIPAL_RANGE with the following values

Should be result set:

Apparently, I can't do left outer join because it doesn't have a common value between the two table from which I could bind the result set. The thing which may tie the two can only be determined through conditional operator other than equal to(=)i.e. IF TBL_PRINCIPAL_MASTER.CODE IS BETWEEN TBL_PRINCIPAL_RANGE.FROM AND TBL_PRINCIPAL_RANGE.TO.Or CODE>=FROM AND CODE<=TO.

Now I am trying to work it out using SQL Calculation View/Procedure/Function, whichever serves the purpose. What I will do is populate TBL_PRINCIPAL_MASTER as my base table and iterate through it, get the CODE and pass it down to TBL_PRINCIPAL_RANGE as parameter.

Any advice will be much appreciated.

Regards,

Rod Castro

P.s. From BO Data Services perspective, this is definitely attainable.

source.png (4.6 kB)
target.png (2.2 kB)

Lars Breddemann
Jul 27, 2017 at 04:41 AM
0

Not too sure where the problem is here.

The following approach appears straight forward to me:

```create column table principal_master
(code integer, name nvarchar(30));

create column table principal_range
(range_from integer, range_to integer, description nvarchar(40) );

insert into principal_master values (2, 'Rod');
insert into principal_master values (8, 'Marc');
insert into principal_master values (5, 'Jan');

insert into principal_range values (1, 5, 'Corporate Principal');
insert into principal_range values (6, 10, 'Regional Principal');

select
pm.*, pr.*
from
principal_master pm
left outer join principal_range pr
on pm.code between pr.range_from and pr.range_to;
```
```CODE	NAME	RANGE_FROM	RANGE_TO	DESCRIPTION
2	Rod	1	        5	Corporate Principal
8	Marc	6	        10	Regional Principal
5	Jan	1	        5	Corporate Principal
```
Share
Former Member Jul 27, 2017 at 05:31 AM
0

Thank you Lars. My problem is having that exact same logic (or similar), implemented into a graphical calculation view. Or should I ditch that idea and use SQL instead?

Share
Former Member Aug 03, 2017 at 08:02 PM
0
Share