3 Replies Latest reply: Jan 18, 2010 8:32 PM by John Witherspoon RSS

    Set Analysis Assigning One dimension to another

      I have two types of transaction and they are completely unrelated. In my data model they are separated. it should have been on a separate app altogether but that's a different story

      Anyway Transactions A and Transactions B. Transactions A has a Company dimension, called ACOMP. Transaction B also has a Company dimension called BCOMP. The values in the Company dimension are the same.

      However on the UI, in only want to ACOMP expose as a filter. But when ACOMP is selected, would like BCOMP to be filtered by that as well.

      I can accomplish this with a set analysis: {$<BCOMP=ACOMP>} . This works as long as some value is selected from ACOMP. However when nothing is selected from ACOMP, then it eliminates any BCOMP values.

      I've done if isnull(ACOMP) then i don't do the set analysis but this crashes my app, presumably because of volume of transactions. Is there some operator of some sort i can use to accomplish this?

      Emerson

        • Set Analysis Assigning One dimension to another
          Julian Villafuerte

          Hi Emerson,

          I'm not sure if I fully understood, but if you have 2 separate transactions with Company as a common field and you want to filter them, what you can do is to link that field between the tables. You can acomplish this in the LOAD script:

           

          [ code ]

          LOAD AComp as Company

          ....

          FROM Source1;

           

           

          LOAD BComp as Company

          ....

          FROM Source2



          [ /code]

           

          If you do this, both will share the Company, and you can select from transaction A or B without problems.

           

          (Correct me if I misunderstood your question)

           

          Good luck!

            • Set Analysis Assigning One dimension to another

              HI Villafuerte,

              Unfortunately that's not the case. In a perfect world, i would link those two company by naming them the same, but for this scenario, my two sets of transaction must act and be affected independently of each other. For example, my dashboard consist of both payroll transactions and sales transactions. When i filter the payroll sheet such as filtering employees, i want to make sure that if the user jumps to the sales sheet, the sales transactions are not affected by the filter done on the employee sheet.

              Since qlikview datamodel would naturally filter things based on how they are connected on my datamodel, i had to completely separate the payroll related tables with the sales related tables. Thus i have to separate their Company ID's as well. Had i connected the company ids by making them the same field name, when i filter the employee, it would leave my entire sales sheet blank since there's no employee related to the Sales.

              So now i have to separate them completely, each has a different company id field name. However now on the UI, i have to present A company selection field. And i wouldn't want to show two different company selection field (one for employee and one for sales) because that would be confusing to the user. I

                • Set Analysis Assigning One dimension to another
                  John Witherspoon

                  This is inefficient, but it should work well for a small data set:

                  {<BCOMP={'$(=concat(ACOMP,chr(39)&','&chr(39)))'}>}

                  I'm not sure if this is quite right, but maybe you could use an IF statement to see if values are selected. If so, use them. Otherwise, default to everything:

                  if(getselectedcount(ACOMP),...{<BCOMP=ACOMP>}...,...)

                  What I've done in practice in a similar situation is write a macro to select the right values in the other field, triggering it onselect or onchange in the main field:

                  sub setBCOMP()
                  ActiveDocument.getField("BCOMP").selectValues ActiveDocument.getField("ACOMP").getSelectedValues
                  end sub

                  Might be able to do the same thing with an action in version 9. We're still on 8.5.