Question Re: Simple Join Issue in Technology Q&A

Simple Join Issue

Former Member — Sat, 14 Nov 2015 10:28:28 GMT

We are in HANA SP9

I need to have a actuals vs target for salesman as follows

Actuals is already available in a calculation view. Target is arriving as flatfile. Hence created analytical view for same

Projection 1 -> target

Projection 2 - > actuals

I joined this 2 projection by salesman code and date and used right outer join ( right ->target, left -> actuals)

and cardinality 1:1

Ideally it should work, but What I am getting is strange output

I played around with cardinality and changes join type to inner/left but nothing giving me correct output

Any inputs much appreciated

Re: Simple Join Issue

Former Member — Mon, 16 Nov 2015 06:24:41 GMT

Hi Ben,

Below are the things I did to achieve the output you were expecting.

1. Created some sample tables and loaded the data you mentioned to get the required output.

create column table target(date_col date, value int);

insert into target values(to_date('01/01/2015','DD/MM/YYYY'), 100);

insert into target values(to_date('01/02/2015','DD/MM/YYYY'), 100);

insert into target values(to_date('01/03/2015','DD/MM/YYYY'), 100);

insert into target values(to_date('01/04/2015','DD/MM/YYYY'), 100);

insert into target values(to_date('01/05/2015','DD/MM/YYYY'), 100);

create column table actuals(date_col date, value int);

insert into actuals values(to_date('01/01/2015','DD/MM/YYYY'), 200);

insert into actuals values(to_date('01/03/2015','DD/MM/YYYY'), 60);

insert into actuals values(to_date('01/04/2015','DD/MM/YYYY'), 150);

insert into actuals values(to_date('01/05/2015','DD/MM/YYYY'), 100);

The below SQL query gives the exact output you are expecting :

select t.date_col, t.value as target, coalesce(a.value,0) as actual from

target t left outer join actuals a

on t.date_col = a.date_col;

Now achieving the same thing using graphical calculation view :

1. Created a graphical calculation view of Dimension type which looks like below :

1. Two projections which will reflect the table data and one join node which does the left outer join of target table with actuals table to get the required output.

Hope this helps

Regards,

Anil

Re: Simple Join Issue

Former Member — Tue, 17 Nov 2015 12:13:15 GMT

Hi Anil, Thanks This is exactly what was done , but the result was coming incorrect Any option to debug it?

Re: Simple Join Issue

former_member200930 — Tue, 17 Nov 2015 18:52:33 GMT

Hi Ben,

Please do following steps to get desired result.

Create calculation view as per the below image

Image 1 :

Join on proper columns. I have used only date columns.

Image 2:

Please create link between join and top aggregation node. As per shown in image 1.

On top level, I have used aggregation node. Please right click on "values" column and select " add to output" for both columns.

Image3:

Now, below image is for your ref.

Image 4 :

Now, activate your model and you will get your desired results.

Image 5:

If you still facing problem or not getting proper result then please coordinate with you HANA admin guy. There could be problem in SAP HANA modeler.

I hope, It will solve your problem.

- Vikram Divekar

Re: Simple Join Issue

Former Member — Wed, 18 Nov 2015 04:06:25 GMT

Hi Ben,

But you have mentioned that the right outer join was done on salesman code and date, that could be the issue. Try taking out salesman code from the join or if you can give us more input it would be helpful.

Regards,

Anil