topic Re: How to find Those id Which fall under two Categories in QlikView

How to find Those id Which fall under two Categories

arvind_patil — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have data as following:

ID Col1 col2 Group

1 11 12 A

2 12 12 A

3 13 12 A

4 11 12 B

5 11 12 A

2 11 11 B

3 11 11 B

I requre Output follows:

ID who are in both group:

ID Col1 col2 Group Final Group

1 11 12 A A

2 12 12 A Both

3 13 12 A Both

4 11 12 B B

5 11 12 A A

2 11 11 B Both

3 11 11 B Both

Other Column (Col1 , col2 )Need Not Consider Please Help me .

Thanks,

Arvind Patil

Re: How to find Those id Which fall under two Categories

antoniotiman — Thu, 21 Sep 2017 08:06:53 GMT

Hi Arvind,

may be this

Table:
LOAD * Inline [
ID Col1 col2 Group
1 11 12 A
2 12 12 A
3 13 12 A
4 11 12 B
5 11 12 A
2 11 11 B
3 11 11 B
](delimiter is spaces);
Left Join
LOAD ID,If(Count(DISTINCT Group) > 1,'Both',Only(Group)) as FinalGroup
Resident Table Group By ID;

Regards,

Antonio

Re: How to find Those id Which fall under two Categories

effinty2112 — Thu, 21 Sep 2017 08:18:42 GMT

Hi Arvind,

Maybe one of these expressions?

ID	Concat(Group,',')	if(count(DISTINCT Group)= 1, Group, 'Both')
1	A	A
2	A,B	Both
3	A,B	Both
4	B	B
5	A	A

Saying Concat(Group,',') rather than 'Both' means there is more flexibility should your data begin to include more groups.

OR you can use calculated dimensions to see things from another point of view.

=Aggr(Concat(Group,','),ID)	Concat(DISTINCT ID,',')
A	1,5
A,B	2,3
B	4

=Aggr(if(count(DISTINCT Group)= 1, Group, 'Both'),ID)	Concat(DISTINCT ID,',')
A	1,5
Both	2,3
B	4

Cheers

Andrew

Re: How to find Those id Which fall under two Categories

arvind_patil — Thu, 21 Sep 2017 09:26:46 GMT

Hi Andrew ,

Thanks for your reply but I want it in script level.

Thanks,

Arvind Patil

Re: How to find Those id Which fall under two Categories

effinty2112 — Thu, 21 Sep 2017 09:33:56 GMT

Try:

Data:

LOAD * INLINE [

ID, Col1, col2, Group

1, 11, 12, A

2, 12, 12, A

3, 13, 12, A

4, 11, 12, B

5, 11, 12, A

2, 11, 11, B

3, 11, 11, B

];

Table1:

LOAD

ID,

Concat(DISTINCT Group,',') as Groups

Resident Data Group by ID;

Table1:

ID	Groups
1	A
5	A
2	A,B
3	A,B
4	B

Regards

Andrew

Re: How to find Those id Which fall under two Categories

arvind_patil — Thu, 21 Sep 2017 09:34:05 GMT

Hi Antonio,

Thanks for your valuable feedback. It works for me.

Thanks,

Arvind Patil