Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
flames
Contributor III
Contributor III

Dimension of a variable as value to another Variable

Hello,

I am creating a kind of lookup table where a person will enter Employee Name and the table should show total salary of his department and company also. Here is an example data and what i wanted to see.

 

Tables i have in qlik sense:  These two are totally independent tables

(complicated to explain y they are independent, not a big deal for now)

TABLE 1
EMP_NAMEDEPARTMENTThi year SALARY
ABCIT500
DEFDB600
GHIIT400
JKLIT100
MNOHR500

 

TABLE 2
DEPARTMENT_2NEXT YEAR REV
IT1200
DB900
HR1000

 

- Now in a sheet, i have a variable to enter Emp name. i will enter ABC in the variable.

- I would like to get the following data as result:

Emp NameEmp TotalDepartmentDepartment Total (This Year)Dept Next Year TotalCompany Total
ABC500IT100012002100

 

I am having difficulty in achieving the final 3 columns. 

3rd col : Dept Total - here Dept will vary based on what EMployee i am searching for.

4th col : Dept Next year : as this is independent table - i should give variable as DEPARTMENT_2 = {ABC's DEPARTMENT from Table 1}.. something like this

5th col: irrespective of my Employee name search, should always display total company wide numbers. 

Appreciate your inputs, Thanks.

1 Reply
Vegar
MVP
MVP

Vegar_1-1587977642030.png

 

Dept Total:

sum(total {<
DEPARTMENT= P({<EMP_NAME = {"$(=vEmployee)"}>}) >} [Thi year SALARY])

Dept Nex Year Total:

sum({<
EMP_NAME = {"$(=vEmployee)"},
DEPARTMENT_2= P({<EMP_NAME = {"$(=vEmployee)"}>}DEPARTMENT) >} [NEXT YEAR REV])

Company Total:

sum(total [Thi year SALARY])

Spoiler

[TABLE 1]:
LOAD * inline [
EMP_NAME, DEPARTMENT, Thi year SALARY
ABC, IT, 500
DEF, DB, 600
GHI, IT, 400
JKL, IT, 100
MNO, HR, 500];


[TABLE 2]:
LOAD * inline [
DEPARTMENT_2, NEXT YEAR REV
IT, 1200
DB, 900
HR, 1000
];

LET vEmployee = 'ABC';