Straight table double dimension

[Kunkulis]

Hi,

I have a Straight table with 3 columns. 2 dimensions and 1 Expression.

The columns I have are the department, the manager and the sum

Problem is that in the qvd file I might have empty cells, which leads to having two or more departments, where in the manager column I will see the manager and below it I will see an empty cell.

I tried adding the manager column as an expression dimension if(manager='','',manager), but it didn't help.

Any idea how this could be solved?  

[albertovarela]

What do you mean by empty cell? is that a blank, a space or a null?  this might lead you to use if(IsNull(Manager).... 

Can you post some sample data?  

[Kunkulis]

I believe the cells are null/blank, because a formula if(Manager='','-',Manager) it does add the '-' in cells. 

In the attachment I made a small version of the issue.  Please keep in mind that the real issue is on a much bigger and more complex scale, the original app has tick boxes where you create the table, but the expressions are constant, so would be nice if the solution would come outside the expressions. Also in the big version there are multiple people columns which could be chosen and they also have null cells.

[arpitkharkia]

Assuming that for every department we have only one manager, i have created a file.

A left join is performed to get the manager names for blank values.

[albertovarela]

Based on the assumption that there is a 1:1 relationship between Department and Manager here is an alternative solution using  the ApplyMap function.

[Kunkulis]

Thank you for your effort, but this solution won't do the trick. 
Is there a solution that wouldn't involve changes to script, but rather work in the straight table.

Because, if there is an empty cell in the *.qvd than it has to be null. 

[Vengatesh · Accepted solution]

Add Manger as an Expression

[arpitkharkia]

If you know all the managers for each department you can use the following dimension

=if(Department='A','Bob',
if(Department='B','Ben'))