2 Replies Latest reply: Nov 9, 2012 12:01 PM by Marcus Malinow RSS

    Checking for the existance of multiple field values in another field

      Hi,

       

      I have an issue with my application and unfortunately seem to have hit a bit of a brick wall.

       

      Users in my system have access to a number of team-level portfolios through a field called UserName. I need to check that this list contains all the values included in another field.

       

      I have an expression I can use to determine all possible values that the user has access to:

       

      concat({$<UserName=,Rank={'Team'}>} DISTINCT UserName)

       

      And another expression that I can use to determine alll possible values:

       

      concat({<DepartmentFlag={1},RankAssocFlag={1}>+<DepartmentFlag={1},RankPartnerFlag={1}>} DISTINCT '- ' & [Department Name])

       

      I need to somehow combine these expressions to determine whether all possible values of the second expression are included in the first. This is complicated by the fact that the second expression is context sensitive so the first list may include items which are not in the second.

       

      For example,

       

      User A:

       

      concat({$<UserName=,Rank={'Team'}>} DISTINCT UserName)

      evaluates to

      '- Construction- Property & Casualty'

       

      concat({<DepartmentFlag={1},RankAssocFlag={1}>+<DepartmentFlag={1},RankPartnerFlag={1}>} DISTINCT '- ' & [Department Name])

      evaluates to

      - Construction- Financial Risks- General Liability & Medical- Professional Risks- Property & Casualty

       

      User B:

       

      concat({$<UserName=,Rank={'Team'}>} DISTINCT UserName)

      evaluates to

      - Commercial Disputes- Construction- Corporate- Employment, Pensions & Incentives- Financial Risks- General Liability & Medical- IPT&O- Other- Professional Risks- Property & Casualty- Real Estate

       

      concat({<DepartmentFlag={1},RankAssocFlag={1}>+<DepartmentFlag={1},RankPartnerFlag={1}>} DISTINCT '- ' & [Department Name])

      evaluates to

      - Construction- Financial Risks- General Liability & Medical- Professional Risks- Property & Casualty

       

      I would want my expression to evaluate to False for User A, as not all values in the second expression are included in the first. And True for User B, as all values in the second expression are included in the first.

       

      Thanks in advance!

       

      Marcus

        • Re: Checking for the existance of multiple field values in another field
          Dave Riley

          Hi Marcus,

           

          I'm not sure if building the concatenated strings first is the right way.  If you want to check existence of a value in another table, FieldIndex might be the way.

           

          Access:

          LOAD * INLINE [

          User, Value

          A, Property

          A, Casualty

          A, Construction

          A, Pensions

          B, Casualty

          B, Financial Risks

          C, Financial Risks

          D, Pensions

          D, Medical

          E, Medical & Clinical];

           

          AllSectors:

          LOAD * INLINE [

          Sector

          Property

          Construction

          Pensions

          Financial Risks

          Medical];

           

          If you use an expression of =Concat(distinct RangeMin(FieldIndex('Sector',Value),1)) then it will return 1 if all values exist, 0 if no values exist and 01 or 10 if mixed results.

           

           

          flipside

           

           

          EDIT:  A better method is to use this expression ...

           

          =if(Concat(distinct RangeMin(FieldIndex('Sector',Value)+1,2)) = 2,'true','false')

           

          .. as the previous suggestion wouldn't test correctly due to the existence of value 01.  This expression only creates recognised integers - 1, 2, 12, 21.

            • Re: Checking for the existance of multiple field values in another field

              Hi,

               

              thanks for that.

               

              The concat wasn't being used as solution, just to check my workings on the way to a solution.

               

              FieldIndex looks to have done the trick though.

               

               

              I used this script

               

              Users:

               

              LOAD * INLINE [

              User, Rank, UserDisplayName, Link

              SVC, Group, 'Parent Group', 1

              SVC, Team, 'Property', 2

              SVC, Team, 'Casualty', 3

              SVC, Team, 'Construction', 4

              SVC, Team, 'Pensions', 5

              SVC, Team, 'Other', 6

              SVC, Partner, 'Fred', 9



              MZB, Group, 'Parent Group', 7

              MZB, Team, 'Casualty', 8

              MZB, Team, 'Financial Risks'

              ]
              ;





              People:

              LOAD * INLINE [

              Link, 'Department Name', DisplayName, RankPartnerFlag, RankAssocFlag

              1, Property, Joe, 0, 1

              1, Property, Fred, 1, 0

              1, Casualty, Bill, 0, 1

              1, Construction, Eileen, 0, 1

              1, Pensions, Marge, 0, 1

              2, Property, Joe, 0, 1

              2, Property, Fred, 1, 0

              3, Casualty, Bill, 0, 1

              4, Contruction, Eileen, 0, 1

              7, Property, Joe, 0, 1

              7, Property, Fred, 1, 0

              7, Casualty, Bill, 0, 1

              7, Construction, Eileen, 0, 1

              7, Pensions, Marge, 0, 1

              8, Casualty, Sylvia, 0, 1

              9, Property, Fred, 1, 0

              ]
              ;

              With a value selected in the field 'User', I can then use this formula to determine whether the user is able to access all possible values of UserDisplayName with a rank of Team

               

              Sum (DISTINCT FieldIndex('Department Name', [Department Name]))

              =

              Sum({$<UserDisplayName=>} DISTINCT FieldIndex('Department Name', UserDisplayName))

               

              With a little tweaking I can use it to determine whether a user has access to various other reporting levels within my dashboard

               

               

              Thanks for your help with this

               

              Marcus