1 Reply Latest reply: Feb 12, 2015 2:01 PM by Oleg Troyansky RSS

    macro to create drill-down groups

    Kristina Stephenson

      Hi All,

      I have 2 or 4 variables for each provider specialty (~50) that I would like to use in a drill down group (2 drill down groups per specialty). For example, a simplified version:

      variables for cardiology, endocrinology, and neurology:

      specialty: cardiology

      var_a_car_tax_desc

      var_a_car_npi_desc

      var_b_car_dept_desc

      var_b_car_prov_desc

       

      specialty: endocrinology

      var_a_end_tax_desc

      var_a_end_npi_desc

      var_b_end_dept_desc

      var_b_end_prov_desc

       

      specialty:neurology

      var_a_neu_tax_desc

      var_a_neu_npi_desc

       

      I want to create the drill-down groups: Cardiology - A, Cardiology - B, Endocrinology - A, Endocrinology - B, and Neurology - A (+ for all other specialties not in example). As you can see by the example, most specialties have both the 2 var_a's and 2 var_b's available but some specialties may only have 2 var_a's available or 2 var_b's available. I have a lookup table that maps the specialty abbreviation in the variable names (car, end, neu) to the specialty. I would like to use this, concatenated with - A or - B as the drill down group name. The drill down groups themselves will look like this:

      - A

      =IF(var_a_car_tax_desc<>'',var_a_car_tax_desc)

       

      =IF(var_a_car_npi_desc<>'',var_a_car_npi_desc)

       

      - B

      =IF(var_b_car_dept_desc<>'',var_a_car_dept_desc)

       

      =IF(var_b_car_prov_desc<>'',var_a_car_prov_desc)

       

      I would like to be able to use a macro and loop to create the - A and - B groups for all specialties (variable naming convention is the same). How should I go about this?

        • Re: macro to create drill-down groups
          Oleg Troyansky

          Hmm, this is a very much open question... Generally speaking, you need to find a copy  of the APIGuide.QVW, and search for the APIs that can be used for generating groups.

           

          Then, you should program your logic in the VBScript macro, retrieve all the necessary variables and use the values in the process of generating groups.

           

          I'm confident that it can be done, but unless someone has done it already, it would be much more work than most experts would be willing to invest...

           

          If you haven't done much with Macro development, I'd highly recommend engaging a developer who is experienced in developing QlikView Macros.

           

          cheers,

           

          Oleg Troyansky

          www.masterssummit.com