Simple Join Issue

[Former Member]

Hi

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

[Former Member]

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

[Former Member]

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

[Former Member]

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