Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Hi Andrew ,
Thanks for your reply but I want it in script level.
Thanks,
Arvind Patil
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
Hi Antonio,
Thanks for your valuable feedback. It works for me.
Thanks,
Arvind Patil