Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.