on 01-12-2015 10:19 PM
Need Help Please
I am trying to join 2 tables in Calculation View of SAP HANA
1) Salary Table
Salary_Slab, Salary_Start, Salary_End
A, 10000, 20000
B, 20000, 30000
C, 30000, 40000
D, 40000, 50000
2) Employee Table
Employee_Number, Salary
01, 15000
02, 18000
03, 32000
04, 45000
The Output I need is the Employee Details with the corresponding Slab
Employee_Number, Salary, Slab
01, 15000, A
02, 18000, A
03, 32000, C
04, 45000, D
Can you please suggest how to Achieve this without writing Scripts.
Hi,
I've made it using calculation view without writing SQLScript.
You can follow the steps below.
1. Create some tables. For simplicity, I just used SYSTEM user to do all the things, creating tables, modeling. It's better to create another user to do it.
CREATE COLUMN TABLE SALARY (
SALARY_SLAB VARCHAR(1),
SALARY_START INTEGER,
SALARY_END INTEGER
);
INSERT INTO SALARY VALUES ('A', 10000, 20000);
INSERT INTO SALARY VALUES ('B', 20000, 30000);
INSERT INTO SALARY VALUES ('C', 30000, 40000);
INSERT INTO SALARY VALUES ('D', 40000, 50000);
CREATE COLUMN TABLE EMPLOYEE (
EMPLOYEE_NUMBER VARCHAR(2),
SALARY INTEGER
);
INSERT INTO EMPLOYEE VALUES ('01', 15000);
INSERT INTO EMPLOYEE VALUES ('02', 18000);
INSERT INTO EMPLOYEE VALUES ('03', 32000);
INSERT INTO EMPLOYEE VALUES ('04', 45000);
GRANT SELECT ON SCHEMA SYSTEM TO _SYS_REPO WITH GRANT OPTION;
2. Create a calculation view, choose dimension in data category. Add EMPLOYEE table to Projection_1 node. Create a calculated column DUMMY, constant value 1.
3. Add SALARY table to Projection_2 node. Also create a calculated column DUMMY.
4. Create Join_1 node. Inner join two projections with DUMMY, so now cross-join.
5. Add Projection_3 node. Add filter to SALARY with filter expression.
6. Connect Projection_3 with the final Projection node.
That's it. Hope you can make it.
Best regards,
Wenjun
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Charit Pal Dhawan,
I did the same way as Wenjun Zhou did except for that filter part, I used the calculated column.
But I feel Wenjun Zhou solution is better than mine.
Happy modeling!
Thanks
Monissha
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.