Different Output Composite Provider / Calculation View

[andreas_lcher]

Hi Experts,

i recognized that i get different outputs from a calculation view with a left outer join than from a composite provider with the same join. In the following screenshot you can see the left outer join of the dso tables ...D017 and ...D027. The join is a left outer join with four conditions as shown in the screenshot.

The following screenshot shows a composite provider with the same join definition as defined in the calculation view:

When i execute both scenarios i get different results in the measures. There is one measure "epqty" of the left table respectively left dso which shows different values. A measure "epanzfind" (which is of the right table respectively right dso) shows identical values in both scenarios.

Output calculation view:

Output Composite Provider:

The reason for the difference is that there are two rows in the right table d027 for which exists only one record in the left table d017. The measure of the left table is counted two times in the calculation view but only one time in the Composite Provider.

In the following screenshots there is a character "epchkcnt" added to the calculation view as well as to the composite provider which shows the difference on detailled Level:

Output Calculation View:

Output Composite Provider:

In the following screenshots the relevant table entries are shown:

As you can see for dataset with epchkcnt = 00001 there are two records in the right table and one record in the left table. For this record the measure "epqty" is counted two times in the calculation view and only one time (which is correct in my opinion) in the Composite Provider.

Do you know how to specifiy the calculation view in a way that it shows identical results to the Composite Provider.

Thank you in advance.

Kind regards,

Andreas

[Former Member]

Hi,

Please check if following helps:

https://looker.com/blog/aggregate-functions-gone-bad-and-the-joins-who-made-them-that-way

Just a question - do you have "FINDING" column from right table in your composite provider? I can't see in your screenshot. You can remove this column in your calculation view while performing join and add it again via one more join at higher level.

Thanks,

Anup

Edit 1: Note that there is something called "Dynamic Join" in the properties of join. If you set it as true, the fields of source tables are aggregated before applying the join (depending which columns are requested in the query - and I suspect your query is not requesting finding column so the join is not applied on this column in case of dynamic join - which I think is precisely done in case of composite provider)

[andreas_lcher]

Hi Roberto,

thank you for your answer. Can you just tell me how to show the SQL Console for the views? Sorry i'm very new to HANA.

Kind regards,

Andreas

[roberto_vacca2]

Hi.

Could you show the SQL Console code for both view? I suppose there's something on the FINDING column data for 00001 occurence..

Hope to help

Bye