Replace a value in a column

[JLL72]

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) 

Could anyone please help?

Thanks

Joe

[marcus_sommer]

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

- Marcus

[JLL72]

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

[Kruger4628]

Thanks for the solution 

[edwin]

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]

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]

here is a better version

[JLL72]

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]

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]

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.