Filter in join for HANA Calculated View

[javier_barneogonzlez]

Hello.

I need create a left join in a HANA calculated View using a filter in the right tabla a field from de left table, for selecting in the right table the correct record with the graphical tool like this:

I have 2 tables, the table A has one ID and one date and the table B has one ID, datefrom and dateto for sereveral jobs.

I need select form the table B wich job is between the date form the table A using the field DATE from the table A as a filter in the table B (DATEFROM <= DATE, DATETO >= DATE) in the join

I explain with an example

TABLE A:

ID_PERSON,NAME,DATE

0001,XXXX,20200102

002,YYYY,20180506

003,ZZZZ,20190108

TABLE B:

ID_PERSON,JOB,DATEFROM,DATETO

0001,JOB1,20190101,20191231

0001,JOB2,20200101,20201231

0002,JOB2,20170101,20171231

0002,JOB3,20180101,20181231

0003,JOB1,20000101,25000101

I need with a left join get the following:

ID_PERSON,NAME,JOB

0001,XXXX,JOB2 -> DATE = 20200102 DATEFROM =20200101 DATETO 20201231

0002,YYYY,JOB3 -> DATE = 20180506 DATEFROM = 20180101 DATETO = 20181231

0003,ZZZZ,JOB1 -> DATE = 20190108 DATEFROM = 200000101 DATETO = 25000101

Is this posible in the graphical tool?

Kind regards.

[venkateswaran_k]

Hi Javier,

To create the calculation view, It can be done using SQL .

Any specific reason you want only in graphical way?

Regards,

Venkat

[javier_barneogonzlez]

Hi Venkateswaran.

All the calculated views are developed with the graphical way, i think it is esier to maintance and

understanding.

Kind regards.

[javier_barneogonzlez]

Hello.

I have an update.

I have try to use a star join with temporal join using the dateto and datefrom as interval and date as filter. This solution runs correctly but i can only use inner join, I need to use left join, becase is possible get some cases without master data but i can't lost the amount.

Kind regards.

[agentry_src]

What have you tried and failed with?

[javier_barneogonzlez]

Hello.

I get the solution with several proyections, i think it is not a optimal solution becasuse i hava some full joins. I think that hana gui is very poor.

I explain my solution: (it can be resumed with lees nodes).

Join_1

Proyection_3

Nodes SI/NO

Join_2

Proyection_4

Proyection_5

Union_1

Aggregation

Kind regads.

[former_member631142]

Hi Javier,

In graphical view, you can achieve this with three additional nodes (join, filter, union) . Please refer below view in image to give you an idea.

If it works for you, please accept the answer.

[javier_barneogonzlez]

Hello,

With this model, you can get less recods than the expected, In the proyection 3 you exlude the records that doen't meet the condition where.

Kind regards.