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

4711 | 500
4712 | 1000

Right table: Quotations

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:

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,

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,

    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