on 11-14-2015 10:28 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.