Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table wich includes the following columns as well as others
ID | Agentkey | trans_num |
There are many cases where for one ID there may be multiple rows
ID | Agentkey | trans_num |
12| 44455544 | 953213213 |
12| 44455521 | 953213213 |
12| 44455522 | 953213213 |
12| 44455540 | 953213213 |
13| 44455544 | 953213213 |
13| 44455503 | 953213213 |
13| 44455512 | 953213213 |
15| 44455544 | 953213213 |
I want to design a filter based on the count of agent keys per ID in this case the available filters would be 4,3 and 1.
4 agent keys associated with ID 12,
3 with ID 13 and
1 with ID 15
What would be the best way to accomplish this?
I thought Count(Total<ID> Agentkey) would work but it doesn't
Any help would be appreciated.
Rob
Check the attachment
Sivaraj, I am currently running personal edition we are working on a server and full setup I cannot view a file created by someone else.
Hi,
Take Id as dimension and expression as count (distinct AgentKey)
Problem here is there are over 6 million IDs. There should be a limited count of distinct agentkeys per ID between 0 and 9
Just take a listbox--> expression --> aggr(count(Agentkey),ID)
Hi,
You can do this:
Go to the ID list box--Properties-Expression tab and add Count(AgentKey) as expression.
This will give you an ID filter with count of each Agent Key.
Regards,
-Khaled.
Hi Robflint,
Sorry I am not getting your requirement ,as per your question above solution works . Can you explain in detailed way
I think I need to state the final result requirement a little clearer. I have over 6 million IDs I only want to see the possible counts of agentkey
and to be able to filter all records that match those counts so if there are 4 million IDs with a count of 3 agentkeys I can click 3 and it will limit my data set to only those records. I don;t need to scroll through 6000000 rows highlighting all the counts of agent key that I want to see.
Hi,
In load script try with this
Load
ID,
Count(Agentkey) AS AgentKeyFilter
Resident Table1
Group by ID;//Table1 is the table with ID,Agentkey,trans_num fields
Add AgentKeyFilter list box in your sheet.