Single selection that checks two different fields for a match

[pink143]

Hi, I have been searching for several days now but I can't find a solution for an internal user request for an app.

Our customer accounts have two officers assigned to each account - Primary Officer and Secondary Officer. In some cases the two fields may have the same officer in both. My user wants one list that combines officers from both fields into one. And then to select an officer from the list and have it return the accounts where that officer is either Primary or Secondary on the account.

For Example, below would be my data set

Account #	Account Name	Account Balance	Primary Officer	Secondary Officer
111	Smith	$100	Fred	Nick
222	Jones	$500	Sam	Sam
333	Miller	$700	Sue	Sam
444	Johnson	$200	Helen	Fred
555	Williams	$300	Greg	Nick

And then the tables below each show the accounts that would be returned with various selections.

Selection:	Nick
Account #	Account Name	Account Balance	Primary Officer	Secondary Officer
111	Smith	$100	Fred	Nick
555	Williams	$300	Greg	Nick

Selection:	Fred
Account #	Account Name	Account Balance	Primary Officer	Secondary Officer
111	Smith	$100	Fred	Nick
444	Johnson	$200	Helen	Fred

Selection:	Sam
Account #	Account Name	Account Balance	Primary Officer	Secondary Officer
222	Jones	$500	Sam	Sam
333	Miller	$700	Sue	Sam

Selection:	Greg
Account #	Account Name	Account Balance	Primary Officer	Secondary Officer
555	Williams	$300	Greg	Nick

Thank you so much in advance! I thought this would be rather simple but once I dug in, I couldn't find a clean solution. I have done tons of searches to find something in Set Analysis and even wondered if I could use Alternate States in a weird way to get what I needed.

[Channa]

i am not sure you can do using front end qlik

but in script of database we can do following

select Distinct PrimaryOfficer  from 

(select  PrimaryOffices from Table

union

select  SecondaryOffices from Table) TableU

this will bring all officers into one column

later you join this view with same Table one time with PrimaryOfficer and secondaryOfficer

OR

-------------

select Distinct ACCOUNTNO,PrimaryOfficer  from 

(select  ACCOUNTNO,PrimaryOffices from Table

union

select ACCOUNTNO, SecondaryOffices from Table) TableU

this case join with account no

hope this will help

[pink143]

Thanks Channa! I asked a coworker to help me add it to my script. He asked what I was trying to do and I explain. He then said I could use GetSelected in the UI. He claims it is easy to setup so I am going to try that first.