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]

This can be done at chart level. It goes something like this:

Create these variables. These can be interfaced with end users with input boxes.

vExcludeEmp = 2

vExcludeDept = D2

vIncludeDept = D1

vExcludeEmpCost = sum({<EmployeeID={"$(vExcludeEmp)"}>} EmpCost

And Make a straight table with Dept and the following expression:

sum(EmpCost)+if(DepartmentID = vExcludeDept,-vExcludeEmpCost,

     if(DepartmentID = vIncludeDept,vExcludeEmpCost,0))

Regards,

Kiran

[#3]

JollyBaba, thx for answer.But by taking the values in variable will work only for one  set of value. What if users want to change more than one relationships.

For example:

EmployeeID 2 moves to Department D2

EmployeeID 4 moves to Department D1

EmployeeID 1 moves to department D3

Please reply!!!!

[swuehl]

Have you looked into input fields? I think you can use input fields for that, maybe create a copy of DepartmentID field in department table as DepartmentIDSim and declare this field as input field upfront.

You should be able to manipulate the data of that field within the UI and compare original data vs. simulated data.

Regards,

Stefan

P.S: Maybe like attached?

[#5]

Hi Swuehl

thx for reply!! Your answer helped me in stage 1, to use Input fields. But still I am missing some part.Input fields alllow me to edit the Field, but after I edit the field in table box,the changes are not reflected in other chart object.

For example,

1) I have table Box,

which contains, DeaprtmentID, EmployeeID and Empcost.I created DeaprtmentID as Input field and i am able to Edit the field, which is good

2) I have chart, which Contains DeaprtmentID,sum(EmpCost).

Once I made the department change in Table Box, corresponding effect should appy in chart as well.Right now even if made department change in table box , there is no change in chart EmpCost value.

Any Idea????

[#6]

While reading the Input field behavior it says, "In Input Fields all values will be considered as distinct, which is different from normal fields in Qlikview.If you for example enter the same value in two positions in the field, they will still appaear as separate values in a list box"

so what it says

D1     E1     2

D2     E2      4

Now if I change d2 to d1

D1     E1     2

D1     E2     4

and now if i create chart , chart will shows like below

D1     2

D1     4

My expectation is

D1     6

any help!!!!!!

[swuehl]

Hi,

have you had the chance to look at my attached sample file (I attached it in an edit, so maybe you missed it)?

I am not an expert in input fields, so maybe I missed some easier solution. But I've also seen that the input field values are different from normal fields, like mentioned in your comment:

You can' just use the input field as dimension and expect that the sums are per value, no they are per record (as you said).

What I did is this: I created a separate field ( I called it something like DepartmentIDClass) which does nothing then serve as a dimension to draw your simulated results against.

Then in the expression I compare the dimension value with the value of each input field record and if equal, sum the linked EmpCost. This seems to work quite nicely.

I also linked the input field via an Key to my fact table. I haven't thought that this is necessary, but I encountered a problem with the input field being filled with numbers like 0 to 3 (something like the record numbers) and I applied this fix I just found here in the forum (fix: use a linked table). Haven't thought about it much yet.

Does this help you somehow? If not, maybe you could post your app here or adapt mine for demonstration of your problem.

Regards,

Stefan

[#8]

Hi Swuehl,

thx a lot for spending time and providing nice useful comments. I have not seen your file , the reason is, currently I am creating POC and I do not have licence version. I will try to find some workaround to see yr file.Again !!Thx for help...

[swuehl]

Ah, I see.

this is the tread I mentioned:

http://community.qlik.com/message/136363

This is how my script looks:

INPUTFIELD DepartmentIDSim;

FACT:

LOAD *, //DepartmentID as DepartmentIDSim;

   RowNo() as InputKey;

LOAD * INLINE [

DepartmentID,          employeeID,     EmpCost

1,                         1 ,                   100

1 ,                         2  ,                  200

2  ,                       3   ,                 250

3   ,                       4 ,                    275

];

CLASS:

LOAD distinct DepartmentID as DepartmentIDClass

resident FACT;

InputField:

Load

      RowNo() as InputKey,

     '1' as DepartmentIDSim // sets 0 as default value in my case

AUTOGENERATE peek('InputKey', -1, 'FACT');

Then I just created a chart with dimension DepartmentIDClass and expression:

=sum(if(DepartmentIDSim=DepartmentIDClass, EmpCost))

I think you already know how to manipulate the input field (in straight table you need a inputsum/inputavg function, but you could also use table box or list box).

Regards,

Stefan

[#10 · Accepted solution]

HI JolyBaba

thx for Answer.What you suggest , is working fine for me, but that approach takes long time when we have millions of data in Table. I found simple solution, When u create a field as Input  field and after that when we change dimension relation ship in Table box and we want to apply the change effect in other controls like different charts, We need to follow below step

1) In any charts or other control, put the dimension-input field as expression, for  above example

we need to put DepartmentID as dimension , but in dimension expression put departmentID as =(DepartmentID)

Once u finish above step you will be able to see expected result.