Skip to Content
0

Option to implement LOOKUP( ) Functionality in HANA Calculation View

Jul 27, 2017 at 03:34 AM

340

avatar image
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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

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
10 |10000 characters needed characters left characters exceeded
avatar image
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
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Aug 03, 2017 at 08:02 PM
0
Share
10 |10000 characters needed characters left characters exceeded