Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

HANA modeling - field comparison to filter records

Hi experts,

I'm trying to create an analytic view on HANA, but I've still have a problem... who look quiet simple !

I'm trying to use arrtibute view and calculation View to calculate

I have a table who give me value, for a Zone, and for a Date. we call it DISC_RATE

AreaValid fromValid ToDiscount
Europe01/01/201531.12.20150.10
Europe01/01/201631.12.20160.15
Asia01/01/201531.12.20150.08

INVOICE table

Customer1Invoice DateAmountArea
TOTO12/12/2015100Europe
TOTO12/12/2016200Europe
TUTU01/01/2016300Europe

I would like to have this result (discount rate depend of Area & Date)

CustomerAmountAreaDateDiscount
TOTO100Europe12/12/20150.10
TOTO200Europe12/12/20160.15
TUTU300Europe01/01/20160.15

In SQL I used to wrote this

SELECT   T1."Customer", T1."Amount", T1."Area", T1."Date", T2."Discount"

FROM      "INVOICE" T1

                 inner join "DISC_RATE" T2 on T1."Area" = T2."Area"

WHERE    T1."Invoice Date"'>=T2."Valid From" and  T1."Invoice Date"'<=T2."Valid To"

=> it was OK.

In Hana Analytic View,

- I cannot create filter like Field A >=Field B.

- I am  not able to write complex join condition

Please, could you help me ?

Tags:
replied

Hello Christophe,

at least there are two options:

1) Create a scripted calculation view in which you can use your "complex" join condition via SQL.

2) Create a graphical calculation view which

  • joins both tables via the Area
  • creates a calculated column on the joined values which calculates if the Invoice Date lies between Valid From/Valid To from the DISC_RATE table -> the result can be e.g. 0 = false, 1 = true
  • filters on the calculated column to just return the joined values with the matching date (e.g. calculated column = 1).

Best Regards,

Florian

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question