topic Re: Filter unique values in QlikView

Filter unique values

Wed, 25 Nov 2020 16:16:04 GMT

Data

UserName	Access ID
100001	52
2232323	53
9750034703	53
3439kljl	52
500003	91
2049400	77
903llddl	52
789oeoeoel	81
9099999	60
30229299	52

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

I want to filter/count all the username having Access id only 52

Next I want it for 53/91.... as on individually

I tried using the formula like below

count({$<Access ID ={"52"}>} username)

I get the data like this

The numbers that I received were not correct because when I did export to excel and cross verified the numbers were different

can somebody helpme as to how to filter data only for the access id 52.

Expected result are

This is just for example

Access ID	Total Count
01 06 11 12 34 52 53	10 5 4 20 25 15 50

Access ID

Total Count

Kindly let me know if you need any additional details

Thanks for your help in advance

Vinay

Re: Filter unique values

Mon, 18 Jul 2016 09:13:42 GMT

Hello Experts,

Does No body knows the answer for my query? please advise .

Atleast give me some thoughts if you would have not come across these type of situations.

Thank You,

Vinay

Re: Filter unique values

oknotsen — Mon, 18 Jul 2016 10:12:15 GMT

It helps if you post your QlikView question in the QlikView area of the community .

I am now moving your topic to "New to QlikView" where it might get the attention it needs.

Re: Filter unique values

PrashantSangle — Mon, 18 Jul 2016 10:17:52 GMT

Hi,

try like

count({<[Access ID]={"52"}>} distinct username)

Regards

Re: Filter unique values

PrashantSangle — Mon, 18 Jul 2016 10:18:49 GMT

take straight table

Access ID as dimension

count(Distinct UserName) as Expression

Regards

Re: Filter unique values

qlikviewwizard — Mon, 18 Jul 2016 12:40:38 GMT

load * Inline

[

UserName,Access ID

100001,52

2232323,53

9750034703,53

3439kljl,52

500003,91

2049400,77

903llddl,52

789oeoeoel,81

9099999,60

30229299,52];

Re: Filter unique values

Anonymous — Mon, 18 Jul 2016 13:50:57 GMT

your expression should be count ( Distinct UserName)

Re: Filter unique values

Tue, 19 Jul 2016 08:26:26 GMT

Hi Max Dreamer,

Thanks for your help it worked partially

Here is another situation where I should not consider the UserName if its already appeared in different Access ID.

Example: There are list of UserName are present in ConID 51 and 65 so, when i use the formula ""count({<[Access ID]={"51"}>} distinct username)"" it filters/picks only for ContID 51 which is great but when i filter for "ContID" 65 it should NOT display the users which are already presented in 51. Therefore, in this example it should be 0.

I tried using <Not Equals> formula to fix this ""count({<[Access ID]={"51 <> 65"}>} distinct username)""/""count({<[Access ID]={"51 -= 65"}>} distinct username)"" the formula works fine no error

but the results are not displayed>

Sorry i was not able to attach the excel sheet so please find the raw data for this example

UserName	ConID
1234333	52
ASDLJLH23	52
9494000	52
8567888L	52
RM222eee	52
z3345554	52
8484848p	52
85757575	52
1200038	52
999033	52
4449999	51
55555999	51
2222229999	51
33333499	51
5555598	51
2222209	51
45666669	51
22222290450	51
4848484	55
676767	55
85757589000	55
6666689	55
33330000	55
666600	55
66600956	55
333348	55
222224	55
4944499	91
11110090	91
4949449	91
333888558	91
95959444	91
77575999	91
20202999	91
494943000	53
56767000	53
29282727	53
4748599	53
99928	53
777480	53
1837456	53
9347874	53
295857	53
298573	53
474747i2	53
4993888277	53
1234333	53
ASDLJLH23	53
9494000	53
8567888L	53
RM222eee	53
z3345554	91
8484848p	91
85757575	91
1200038	91
999033	91
4449999	65
55555999	65
2222229999	65
33333499	65
5555598	65

I did a reasearch to find solution for this issue but not found anything exactly

Kindly let me know if you need any additional details for this

Re: Filter unique values

Tue, 19 Jul 2016 08:28:44 GMT

This is little urgent can somebody provide me an ideas please

Moderators could you please Approve my previous replay at your earliest.

Thank You,

Vinay

Re: Filter unique values

Tue, 19 Jul 2016 17:03:01 GMT

This was almost near to my query.

Thanks a lot and appreciate for your help.This answer works when you use set analysis