Obtain the intersection of variables

[#1]

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

[gandalfgray]

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

[#3]

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

[gandalfgray]

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

[#5]

Hi Jay,

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

For example something like this:

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

Stefano.

[#6]

Stefano,

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

Regards

Jason

[#7]

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

[#8]

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

[#9]

try this jason.

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

C u,

Stefano.

[#10]

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