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


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