Skip to Content
0

Bring only one record to the output of HANA View in case of 1:n cardinality of two tables

Jul 29, 2017 at 01:03 PM

36

avatar image
Former Member

Hello experts,

I would like to join two tables.

Left table: Orders

ORDER_NO (Key) | NET_VALUE
4711 | 500
4712 | 1000


Right table: Quotations

QUOTATION_NO (Key) | ORDER_NO
11-01 | 4711
11-02 | 4711
11-03 | 4711
12-01 | 4712
12-02 | 4712
12-03 | 4712
12-04 | 4712

To join both tables it´s necessary to use the field ORDER_NO

The output of the calculation view should looks like this:

ORDER_NO | QUOTATION_NO | NET_VALUE
4711 | 11-03 | 500
4712 | 12-04 | 1000

So at first it´s necessary to sort the data records in the Quotations table to get the newest Quotation no. (e.g. 12-04 instead of 12-03, 12-02, 12-01) and bring only one quotation to the output.

How should I build up the join or the settings in the Calculation view?

Many thanks in advance!

Best regards,
Michael

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
avatar image
Former Member Jul 30, 2017 at 07:08 PM
0

Hello everyone,

I´ve fixed the issue with the help of a "Rank Node".

Source Table:

Calculation View (Data Category Dimension) with a Rank Node to sort the quotations and select only the newest in case of 1:n cardinality with order no:

Rank Node Settings:

Output of the Calculation view:

With the help of the calulation view, with the 1:1 mapping between quotation no. and order no., now I´m able to join the new "mapping view" 1:1 with the main order table view to enhance the order values (net value, etc.) with the information of the newest quotation.

Best regards,
Michael


kd2gh.png (3.1 kB)
mj2zp.png (7.7 kB)
g132r.png (9.2 kB)
ukhpg.png (6.9 kB)
Share
10 |10000 characters needed characters left characters exceeded