Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Obtain the intersection of variables

Hi,

I have an employee database listing (amongst other things) the employee’s grade and skills.

I wish to be able to select a number of skills and possibly the employee grade to filter down those employees that match my criteria. In other words I want the intersection of my selections.

I thought it would be best to set up variables for my selections (vSkill1, vSkill2 and vGrade) and have an input box to set these.

I am struggling to obtain the results of the intersection

I found the following  aggregation calculation that works with hard codes field values, but not with variables  vSkill1 and vSkill2 :

=if(aggr(count({1< Skill = {'ACII'} >} [EMPLOYEE.Last Name]),[EMPLOYEE.Last Name])>=1 and aggr(count({1< Skill = {'COGEN'}  >} [EMPLOYEE.Last Name]),[EMPLOYEE.Last Name])>=1,[EMPLOYEE.Last Name])

And I tried using set analysis I found:

= $( *=vSkill1()  {vSkill2()}) [Employee.Last Name]

Or

= {$ < vSkill1()  *= {‘vSkill2()’}>} [Employee.Last Name]

Neither worked.

Can someone offer a solution or an alternative method of obtaing the intersection of the selections ?

Regards

Jason

9 Replies
Specialist II
Specialist II

Hi Jay

From your description I do not understand why you want to do it with set analysis at all?

It sounds like you can do that with just simply selecting in the fields Skill and Grade.

/gg

Not applicable

Hi GG,

I can get an intersection of results for two different fields but if I select two Skills I get the union and not the intersection (i.e. 10 people have one skill, 5 people have another skill and only one person has both - I want the one person highlighted, but the results I get return 14 people highlighted)

Regards

Jason

Specialist II
Specialist II

Yes, on second thoughts I suspected  that was what you wanted.

You can do it if you use an "and mode" field.

See 12.6 in the QV documentation

/gg

Not applicable

Hi Jay,

have you tried with '*' operator in Set Analysis?

For example something like this:

sum({<Set analysis>*<Set analysis> } Field)

Stefano.

Not applicable

Stefano,

I have tried using set analysis and *, but unfortunatley I could not get it to work using variables.

Regards

Jason

Not applicable

Jason,

in 1 of mine project i use this:

{$<TP_DOC_BS={$(#TP_DOC_ESCLUSI)}>}

TP_DOC_ESCLUSI is a variable with a lists of value entered manually.

It works, so should works something like this:

{$<vSkill1={$(#ListSkill1)}>*<vSkill2={$(#ListSkill2)}>....}

C u

Not applicable

Stefano,

I think my ignorance is beginning to show!

If I use    = {$< vSkill1={$(#ListSkill1)}>*<vSkill2={$(#ListSkill2)}>}

The editor says ‘Expression OK’ , but there is a red line under all of the  statement and vSkill1 and vSkill2 are not in grey (indicating that they are not recognised?, but #ListSKill1 and #ListSkill2 are in grey (indicating they are recognised?).

My vSkills variables have values that I have added using an input box.

I do not get any return when there is one person that matches the skills I have entered.

Whether I have no selections or I select the two skills manually (using a list box) I do not get a result from the equation.

Do I need to create variables for the #ListSkills or are they created dynamically, and do I enter values into the #ListSkills rather than vSkills?

Regards

Jason

Not applicable

try this jason.

'#' is not rilevant in this case, it works with and without '#'.

C u,

Stefano.

Not applicable

Hi Stefano,

Thanks for the file. It is definitely progress but I am still struggling to achieve what I want to.

With your solution I would like to be able to select VALUE where the selections in vList1 and vList2 coincide. I tried the following but it did not work:

=SUM({$<{$(#vLIST1)={$(#vLIST2)}>*<{$(#vLIST1)={$(#vLIST2)}>}VALUE)

In addition to this I tried using your set analysis with my database and I found I could match a variable with a table value as long as it was numeric.

e.g. =SUM({$<EMPLOYEE.Rating={$(#vType1)}>*<EMPLOYEE.Rating={$(#vType1)}>}EMPLOYEE.Rating)

where vType1 = 2

However, if my input criteria was character based it did not find the corresponding matches.

e.g. =SUM({$<Skill={$(#vSkill1)}>*<Skill={$(#vSkill1)}>}Skill)

where vSkill1 = ACII

I have tried:

  • -      Putting ‘ ‘ around the input value – i.e. ‘ACII’
  • -      I tried putting ‘ ‘ around the Skill column name in the equation
  • -      I tried putting ‘ ‘ around #vSkill1 in the equation
  • -      I tried using TRIM on the load of Skill

None of the above worked. Do you have any suggestions?

Many thanks for your help.

Jason