HANA modeling - field comparison to filter records

[christophe_averous]

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

Area	Valid from	Valid To	Discount
Europe	01/01/2015	31.12.2015	0.10
Europe	01/01/2016	31.12.2016	0.15
Asia	01/01/2015	31.12.2015	0.08

INVOICE table

Customer1	Invoice Date	Amount	Area
TOTO	12/12/2015	100	Europe
TOTO	12/12/2016	200	Europe
TUTU	01/01/2016	300	Europe

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

Customer	Amount	Area	Date	Discount
TOTO	100	Europe	12/12/2015	0.10
TOTO	200	Europe	12/12/2016	0.15
TUTU	300	Europe	01/01/2016	0.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 ?

[pfefferf · Accepted solution]

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

[christophe_averous · Accepted solution]

Thanks Florian,

I have created a calculated colum, and it's works.

Regards,