Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JLL72
Contributor II
Contributor II

Replace a value in a column

Hi Guys,

 

Is it possible to replace a value in a column ?

This is my data:

lets say Employee A is from the Sales Department, is it possible to have a drop down for department which overrides his actual department?

i.e. in the override drop down if we select HR, the data will assume employee A is from HR instead of Sales. and this will then change my expression in the table ( avg.rate by department as he is now in HR not Sales) 

JLL72_0-1605812936764.png

 

Could anyone please help?

 

Thanks

Joe

 

1 Solution

Accepted Solutions
edwin
Master II
Master II

after giving it some thought, i removed the employee island table and changed the expression.  see attached.

one expression did get a little bit more complex but simplified the other.

View solution in original post

14 Replies
marcus_sommer

I think you should ensure the proper matching within the datamodel.

- Marcus

JLL72
Contributor II
Contributor II
Author

This is to give the users the flexibility to change the data and see what is the implications of it

Kruger4628
Contributor
Contributor

Thanks for the solution 

edwin
Master II
Master II

is the objective to determine hourly rate per department if specific employees were moved to a different department?  assuming analysis allows only if there is only one new department

the user experience is select specific Employees, then select where to move them.

the data model should allow for all combinations of employees and departments - this will allow you to choose the employees and department for the what if.  the chosen department should be in an island table to not interfere with the other expressions.  the chosen department is saved in a variable.  the first chart shows how employees are separated (selected and not selected)

If A and C are chosen and moved to HR, the data set {1-$} will pull out only the ones that arent selected.  then for the selected employees, add in your set analysis Depart={'$(ChoseDepartment)'}
the idea is compute average of departments excluding selected employees.  then 

edwin
Master II
Master II

you cant use AVG function as you cant add AVGs.  you total the rates then divide by the count.  the first chart shows how to compute for the 2 datasets. (1st is not selected, 2nd is selected but with new department)

edwin
Master II
Master II

here is a better version

JLL72
Contributor II
Contributor II
Author

Thank you Edwin, this is exactly what im looking for. just wondering if there is another way to do this? without duplicating the records in load script. cus i have a large dataset.

edwin
Master II
Master II

we will still need an association between all employees and other departments.  without duplicating the entire data set, create a new table with the cartesian join of departments only.  classify these into actual departments and WHATIF scenarios.

create a separate table with just employees - this will facilitate the selection for the user.  employees to move.

a distinct of employees + cartesian join of just the departments should be smaller that cartesian join of whole table.

see attached.  this shows you how to select the actual data less the selected employees + selected employees in the new department.  jut fill in the calculation for averages

edwin
Master II
Master II

just curious, how many records are we talking about when you say huge data set.  i would cap the original solution at under 100M records - that should still be manageable with QV, remember your fact table may have lots of dimensions but you do not need to join all the fields, just the ones you need so it should be a long but lean fact table.