HANA modeling - field comparison to filter records
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|
I would like to have this result (discount rate depend of Area & Date)
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 ?
Florian Pfeffer replied
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).