Kind of a hard one to solve because you can't share your real data, but I'll give it a shot.
Would it be possible to solve this in script? First option:
Table1:
LOAD * Inline [
ID, Specialist1
1, Bill
2, Bill
3, Bill
4, Bob
5, Joe
6, Joe
7, Joe
8, Joe];
Table2:
LOAD * Inline [
ID, Specialist2
3, Bill
9, Bill
10, Bill
11, Bill
12, Bill
13, Bob
4, Bob
5, Joe
6, Joe
7, Joe];
Table3:
LOAD ID,
Specialist1 as Specialist
Resident Table1;
LOAD ID,
Specialist2 as Specialist
Resident Table2;
What I did:
- Load sample data
- Load ID (and any other common fields needed for this expression) and Specialist1 as Specialist
- Same thing again, but with Specialist2
- These concatenate into one table with Specialist, ID, any other common fields
- Dimension in table: Specialist
- Expression in table: COUNT({<set analysis>} DISTINCT ID)
Second option:
If you have a lot of data, you probably don't want to add another table containing millions of records to the data model. In that case you could do the calculation in script as well. However, you can't use set analysis in script, so if you need that you'd need to work with an IF (there are probably better solutions but I'm not sure what they are, still fairly new to Qlikview myself).
Anyway for a simple Count just add this to the script above:
Table4:
LOAD Specialist,
Count(DISTINCT ID) as Count
Resident Table3
Group by Specialist;
Drop table Table3;
The table then simply has Specialist as dimension, and Sum([Count]) as expression. Table4 has way less records than Table3 assuming a lot of data in your source.
Problem is, this creates a data island, which you probably don't want either. You can connect it back to the data model by adding 'Specialist as Specialist1' to the load in Table4 (as well as Specialist on its own), but this would create a 'bad' association, so you'd have to add set analysis like 1<Specialist1=$::Specialist1> or something like that to any other expression in the app, as otherwise you'd get wrong answers when users make a selection.
Sorry for the wall of text. This probably isn't the full answer to your problem, but I hope it's of some use to you anyway.
