Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

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

    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

    Add comment
    10|10000 characters needed characters exceeded