Processing a single table in HANA gives strange results

[Former Member]

Nice to meet you all.

Use case:

I have a single table

EmpId	EmpName	EmpDept	Supervisor
E1	Sam	Physics	E2
E2	Ram	Chemistry	E3
E3	San	Maths	E2
E4	Kumar	Biology	E2
E5	Ravi	Tamil	E2
E6	Guru	English
E7	Sankar	Hindi	E2
E8	Mani	Computer	E1
E9	Selva	Electronics	E4
E10	Ramesh	Electrical

The Supervisor is also an employee. Hence the Supervisor column has the EmpId of the Supervisor.

I need to find the EmpName Vs SupervisorName details

Following steps I did,

Step 1:

Created an attribute view, where I added the above table twice to get the SupervisorId and SupervisorName. The results are expected

Step 2:

Created an analytic view. Joined the above attribute view and the table.

But the actual result is wrong. For example, the supervisor name for E2 is Ram as per step 1 and table, but the result shows it as Kumar. None of the SupervisorId and SuperVisorName matches.

Please advice.

[muthuram_shanmugavel2]

Hi Guru,

if you notice step1 results,

Employee "E2" records are getting duplicated.

Because of this Analytic View results are wrong.

We can get this results simply by using SQL scripts.

using this SQL , You can create SQL view and would use SQL view in Calculation Views.

Else You can create scripted calculation view.

Select A.EmpID, A.EmpName, A.EmptDept, A.Supervisior, B.EmpName

From HIERARCHY A

Left Outer Join

HIERARCHY B

On A.Supervisior = B.EmpId

Regards,

Muthuram

[former_member182465]

Hi Muthu,

Just logged in to share sql script but saw you have already done it.

[former_member182465]

Hi Guru,

You should have posted this question in Inmemory HANA group not HANA use cases group.

There are lot of ways to resolve your issue. The procedure I used is as below.

1) created calculated view with Dimension type instead of Cube

2) Created 3 projections using Employee table

Projection 3 has filter as below

3) Then create the flow as below

Join Proj 1 and Proj 2 for getting empid etc to supervisorid and Supervisor Name

union the above output to Proj 3 to get all records. As proj 3 has records where supervisorid is blank as this records will be missed in inner join.

Join

Union

Final Projection below semantics

Semantics - Rename the below highlighted ones

4) Final Output

Experts please let me know if this procedure is not recommended.

You can also keep the filters on Proj 1 and Proj 2 with supervisor <> blank.

Guru try this and let me know if you face any difficulty.

Close this thread if your issue resolved.