Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
flipside
Partner - Specialist II
Partner - Specialist II

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.

View solution in original post

2 Replies
flipside
Partner - Specialist II
Partner - Specialist II

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.

Not applicable
Author

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