topic Re: Multiple filter or using multiple conditions in QlikView

Multiple filter or using multiple conditions

Mon, 24 Apr 2017 09:06:04 GMT

Hello Experts

I have done lot of research in the QlikView community to find the solution but didn't find correct solution....Can somebody help me on this....this is little urgent need to find a logic.

I have a list of UserName (Field) aligned to ID(Field) but the UserNames are duplicated and ID are Unique say for example:

UserName ID

ABC 1

DBC 2

ARP 3

ABC 4

DBC 5

ARP 6

But I need to find a count or getcount of Distinct users

Example: 1 - If ABC is considered in ID (1) then he should not be considered in ID (4)

Example: 2 - If DBC is considered in ID (2) then he should not be considered in ID (5)

(1) I tried with this formula =Count(UserName = {<ID={'1'}>}) But it's now working

(2) =Count(Distinct UserName = {<ID={'2'}>} =- {{<ID={'5'}>}})

Even this didn't work.

(3) Count([UserName] OnlyIf {<ID={'2'},>}) This also didnt work

Thanks a lot in advance

Re: Multiple filter or using multiple conditions

rahulpawarb — Mon, 24 Apr 2017 09:21:49 GMT

Hello Vinay,

Please try below:

=Count({<ID={1}>} DISTINCT UserName)

Please let me know if you still won't get desired result.

Regards!

Rahul

Re: Multiple filter or using multiple conditions

Anil_Babu_Samineni — Mon, 24 Apr 2017 09:31:30 GMT

Vinay Chandra Nagaraju wrote:

But I need to find a count or getcount of Distinct users

Example: 1 - If ABC is considered in ID (1) then he should not be considered in ID (4)

Example: 2 - If DBC is considered in ID (2) then he should not be considered in ID (5)

That is the case, You just use Min(ID) or FirstSortedValue(ID, Aggr(Sum(ID), UserName))

Re: Multiple filter or using multiple conditions

awhitfield — Mon, 24 Apr 2017 09:33:13 GMT

=Count( DISTINCT UserName)

But I am not sure what you are actually trying to achieve??

Andy

Re: Multiple filter or using multiple conditions

qlikview979 — Mon, 24 Apr 2017 09:44:05 GMT

Not sure if u r expecting output like

ID,UserName, Count(UserName)

1 ,ABC, 1

2 ,DBC, 1

3 ,ARP, 1

6 ,ARP, 1

UserName:-if(ID<>4 and ID<>5,UserName)

COunt(UserName):-Count(if(ID<>4 and ID<>5,UserName))

Re: Multiple filter or using multiple conditions

Mon, 24 Apr 2017 10:22:17 GMT

Hi Rahul Pawar,

Many thanks for your response in the first place however, the formula you provided me was helpful but it was considering the UserName also which was present in the other ID's.

Basically I want to do multiple filter. 1st time I want Distinct count of UserName for respective ID this I was able to achieve but the Major problem is If "X" UserName is considered in ID number "1" then the next preceding ID number "2 or 3 or 4" should not pick/consider the "X" UserName .

Hope I am clear with this if not please let me know I will try to provide more examples.

Just FYI... I have attached the sample data kindly have a look and let me know your thoughts

This urgent please help.

Thank You,

Vinay

Re: Multiple filter or using multiple conditions

Tue, 25 Apr 2017 04:38:04 GMT

Hi Anil,

Many thanks for your response in the first place however, the formula you provided me was helpful but it was considering the UserName also which was present in the other ID's.

Hope I am clear with this if not please let me know I will try to provide more examples.

Just FYI... I have attached the sample data kindly have a look and let me know your thoughts

This urgent please help.

Thank You,

Vinay

Re: Multiple filter or using multiple conditions

Tue, 25 Apr 2017 04:40:16 GMT

Hi Andrew,

Many thanks for your response in the first place however, the formula you provided me was helpful but it was considering the UserName also which was present in the other ID's.

Hope I am clear with this if not please let me know I will try to provide more examples.

Just FYI... I have attached the sample data kindly have a look and let me know your thoughts

This urgent please help.

Thank You,

Vinay

Re: Multiple filter or using multiple conditions

Tue, 25 Apr 2017 04:41:22 GMT

Hi Mahesh,

Many thanks for your response in the first place however, the formula you provided me was helpful but it was considering the UserName also which was present in the other ID's.

Hope I am clear with this if not please let me know I will try to provide more examples.

Just FYI... I have attached the sample data kindly have a look and let me know your thoughts

This urgent please help.

Thank You,

Vinay

Re: Multiple filter or using multiple conditions

Anil_Babu_Samineni — Tue, 25 Apr 2017 04:56:09 GMT

If possible, Can you make excel formula for desired result. So that we may try in QV

Re: Multiple filter or using multiple conditions

Tue, 25 Apr 2017 06:33:23 GMT

Hi Mahesh,

Thanks for your response in the first place.

However, the ID (6), UserName (ARP) and Count(UserName) should be (0).

Because ARP we have already considered in ID (3), hope i am clear.

ID,UserName, Count(UserName)

1 ,ABC, 1

2 ,DBC, 1

3 ,ARP, 1

6 ,ARP, 0

Kindly, let me know how to get the output

Thank You,

Vinay