1 Reply Latest reply: May 26, 2016 7:13 AM by Sunny Talwar RSS

    Aggregating Multiple "Yes/No" Fields

    Todd Johnston

      Hello Community,

       

      I have a client list and 3 "Yes/No" Fields indicating whether or not a client has any of three products.  For this example, lets say the products are Apples, Oranges, and Grapes.  I would like to create a new field (Fruit Customer) indicating only the clients who have at least 1 of the products.  I know in Excel this is achievable via If/Or expression, however I cant seem to figure out a Qlik approach.

       

      Any help would be appreciated.

       

      Thanks,

       

      TJ

        • Re: Aggregating Multiple "Yes/No" Fields
          Sunny Talwar

          You can create flag to find clients which are Yes for only one product:

           

          Table:

          LOAD *,

          If(SubStringCount(Apples&'|'&Oranges&'|'&Grapes, 'Yes') = 1, 1, 0) as Flag;

          LOAD * Inline [

          Client, Apples, Oranges, Grapes

          A, Yes, Yes, No

          B, Yes, No, No

          C, No, Yes, Yes

          D, No, Yes, No

          ];

           

          Now use this flag in the set analysis or if statement to anywhere you would want to get only those clients where they have Yes for only one place