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

[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

[Former Member · Accepted solution]

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