topic How to Change dimension Relationship-What if Scenario in QlikView

How to Change dimension Relationship-What if Scenario

Thu, 08 Sep 2011 21:12:00 GMT

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

Re: How to Change dimension Relationship-What if Scenario

Fri, 09 Sep 2011 04:41:53 GMT

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

Re: How to Change dimension Relationship-What if Scenario

Fri, 09 Sep 2011 13:58:07 GMT

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!!!!

Re: How to Change dimension Relationship-What if Scenario

swuehl — Fri, 09 Sep 2011 14:13:17 GMT

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?

How to Change dimension Relationship-What if Scenario

Fri, 09 Sep 2011 17:55:57 GMT

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????

How to Change dimension Relationship-What if Scenario

Fri, 09 Sep 2011 19:50:51 GMT

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!!!!!!

Re: How to Change dimension Relationship-What if Scenario

swuehl — Fri, 09 Sep 2011 20:50:46 GMT

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

Re: How to Change dimension Relationship-What if Scenario

Fri, 09 Sep 2011 20:56:17 GMT

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...

How to Change dimension Relationship-What if Scenario

swuehl — Fri, 09 Sep 2011 21:03:45 GMT

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

Re: How to Change dimension Relationship-What if Scenario

Tue, 13 Sep 2011 14:09:39 GMT

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.

Re: How to Change dimension Relationship-What if Scenario

Tue, 13 Sep 2011 14:15:46 GMT

Yes, that works.

Regards,

Kiran

Re: How to Change dimension Relationship-What if Scenario

swuehl — Tue, 13 Sep 2011 14:22:44 GMT

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

Re: How to Change dimension Relationship-What if Scenario

Tue, 13 Sep 2011 15:48:22 GMT

HI swuehl,

Please see attached file.

Re: How to Change dimension Relationship-What if Scenario

swuehl — Tue, 13 Sep 2011 15:54:37 GMT

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

Re: How to Change dimension Relationship-What if Scenario

Wed, 18 Jan 2012 15:55:43 GMT

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?

Re: How to Change dimension Relationship-What if Scenario

Wed, 18 Jan 2012 16:30:18 GMT

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

Re: How to Change dimension Relationship-What if Scenario

Wed, 18 Jan 2012 16:32:27 GMT

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

Re: How to Change dimension Relationship-What if Scenario

Wed, 18 Jan 2012 17:14:07 GMT

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.