How to Change dimension Relationship-What if Scenario

[#1]

I have two tables in my source system and I loaded  these tables in Qlikview

Table Name and Table field information

1)Table Name: Dept_Emp

Field names:

                     DepartmentID

                     EmployeeID

2)  Table Name: EmpCost

Field Name: EmpID

                   EmpCost

So table data looks like

DepartmentID          employeeID     EmpCost

D1                         1                    100

D1                          2                    200

D2                         3                    250

D3                          4                     275

DepartmentCost=Sum(EmpCost)

Now I want to see what will be the cost  impact on all the departments , if EmployeeId 2 moves to Department D2 and Employeeid 4 moves to department D1.

After making above changes, I would like to see the DepartmentCost reflection in chart or table view, where I can see

DepartmentID     DepartmentCost

D1                    375

D2                   450

D3                    0

[#2]

Yes, that works.

Regards,

Kiran

[swuehl]

Hi programmer123,

it's good to hear that you got it working and even found a simple solution.

I just didn't get what you do with  the dimensions and expression (like =(DepartmentID) ).

Could you post your soulution here? We are able to open your personal edition file.

Thanks for sharing,

Stefan

[#4]

HI swuehl,

Please see attached file.

[swuehl]

Ah, didn't know that bracket thing, thanks for sharing again!

[#6]

Hi programmer123, I have exact same scenario as yours, and this discussion thread realy helped me. However, Im facing one issue which i guess you didn't. So I have made DeptID column editable, and EmpID and EmpCost columns non-editable. So if user wants to change the dept that an employee belongs to, she must find the emp in the table box, and change the corresponding DeptID value.
So in a pie-chart, Sum(EmpCost) is shown grouped by DeptID.

But the problem im facing is this. When user changes the DeptId of an employee, say moves EmployeeId2 from dept D1 to D2, in the pie-chart, though the Sum for D1 decreases by 200, the sum for D2 doesnt increase. Instead a new pie is inserted with same name D2 with sum = cost of EmployeeID2(i.e. 200 )

Why is it not grouping the names together? Can you please help?

[#7]

Hi RajaYaad,

Few pointers,

1) in this case , change in dept Id is case sensitive , so d2 and D2 will be treated differently, so please chek that.

2) Department ID field in Pie chart should be like :    =(DeptID), please see my attached example in previous thread.

3) If 1 and 2 is not working , please send me the qlikview  sample file.

if it works for you , please mark the answer  as correct answer

[#8]

Hi RajaYaad, Few pointers, 1) in this case , change in dept Id is case sensitive , so d2 and D2 will be treated differently, so please chek that. 2) Department ID field in Pie chart should be like :    =(DeptID), please see my attached example in previous thread. 3) If 1 and 2 is not working , please send me the qlikview  sample file. if it works for you , please mark the answer  as correct answer

[#9]

Thank you so much for quick reply. And yes, I satisfy your first two points. But still not working. So I went to create a sample qvw to post here(I cant obviously post company's sensitive data here :-).

But the sample works. I dont know what the difference could be. The data structures are same, one table with col A and Col B, and another table with Col B and Col C. And both tables are loaded from seperate excel sheets. I will paste my sample script here :

INPUTFIELD Dept;

LOAD Dept,

     Emp

FROM

Dept_Emp.xlsx

(ooxml, embedded labels);

Directory;

LOAD Emp,

     EmpCost

FROM

Emp_EmpCost.xlsx

(ooxml, embedded labels, table is Sheet1);

This works, and my actual data is same as this, except different column names from different excel sheets. I guess the cell text formatting in my real excel sheet is messed up(maybe extra whitespace or something). I will check that. Thanks though.