17 Replies Latest reply: Jan 18, 2012 12:14 PM by rajayaad RSS

    How to Change dimension Relationship-What if Scenario

    saurabh desai

      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

          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
              saurabh desai

              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
                  Stefan Wühl

                  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
                      saurabh desai

                      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
                          saurabh desai

                          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
                              Stefan Wühl

                              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

                      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
                          saurabh desai

                          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
                            saurabh desai

                             

                             

                            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

                                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.