Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
assume I have the following data:
Load
ID,User,Pet
1,A,Cat
2,A,Dog
3,B,Dog
What I want to achieve:
I want to have a new column in this table which does the following:
- see if the user has a dog. if yes, check if the same user also has a cat. if yes, write 'Yes' in the new column 'Multiple'. if not, leave the column 'Multiple' empty.
Can someone help me with this statement? Thanks a lot in advance:
AAA:
LOAD * Inline [
ID,User,Pet
1,A,Cat
2,A,Dog
3,B,Dog
4,B,Mouse
5,C,Cat
6,C,Mouse
];
Left Join
LOAD User, '1' as Temp Resident AAA Where Pet='Dog';
Left Join
LOAD User, '1' as Temp1 Resident AAA Where Pet='Cat' and Temp='1';
BBB:NoConcatenate
LOAD ID,User,Pet,If(Temp1='1' and Pet='Dog', 'Yes', '') as Multiple Resident AAA;
DROP Table AAA;
AAA:
LOAD * Inline [
ID,User,Pet
1,A,Cat
2,A,Dog
3,B,Dog
];
Left Join
LOAD User, If(Count(DISTINCT Pet)>1, 'Yes', '') as Multiple Resident AAA Group By User;
Thanks for the response. I'm sorry, but now I see that I was a little bit unspecific. I only want to write "Yes" if a dog owner has a cat as well (other pets do not count)
assume I have the following data:
Load
ID,User,Pet
1,A,Cat
2,A,Dog
3,B,Dog
4,B,Mouse
5,C,Cat
6,C,Mouse
What I want to achieve:
I want to have a new column in this table which does the following:
- see if the user has a dog. if yes, check if the same user also has a cat. if yes, write 'Yes' in the new column 'Multiple'. if not, leave the column 'Multiple' empty.
The result should be:
ID,User,Pet,Multiple
1,A,Cat,
2,A,Dog,Yes
3,B,Dog,
4,B,Mouse
5,C,Cat
6,C,Mouse
Hi,
this should work:
User:
Load * Inline [
ID,User,Pet
1,A,Cat
2,A,Dog
3,B,Dog
4,B,Mouse
5,C,Cat
6,C,Mouse
];
NoConcatenate
UserWithDogAndCat:
LOAD User,
'Yes' as Multiple
Resident User
Where Pet = 'Dog';
Inner Join
LOAD User
Resident User
Where Pet = 'Cat';
Right Join
Load *
Resident User;
DROP Table User;
May be this in that case (Making changes to Alexandros17 script )
AAA:
LOAD * Inline [
ID,User,Pet
1,A,Cat
2,A,Dog
3,B,Dog
4,B,Mouse
5,C,Cat
6,C,Mouse
];
Left Join
LOAD User,
If(Count(DISTINCT Pet)>1, 'Yes', '') as Multiple
Resident AAA
Where Match(Pet, 'Dog', 'Cat')
Group By User;
AAA:
LOAD * Inline [
ID,User,Pet
1,A,Cat
2,A,Dog
3,B,Dog
4,B,Mouse
5,C,Cat
6,C,Mouse
];
Left Join
LOAD User, '1' as Temp Resident AAA Where Pet='Dog';
Left Join
LOAD User, '1' as Temp1 Resident AAA Where Pet='Cat' and Temp='1';
BBB:NoConcatenate
LOAD ID,User,Pet,If(Temp1='1' and Pet='Dog', 'Yes', '') as Multiple Resident AAA;
DROP Table AAA;
Slight change based on the output required:
AAA:
LOAD * Inline [
ID,User,Pet
1,A,Cat
2,A,Dog
3,B,Dog
4,B,Mouse
5,C,Cat
6,C,Mouse
];
Left Join
LOAD User,
'Dog' as Pet,
If(Count(DISTINCT Pet) > 1, 'Yes') as Multiple
Resident AAA
Where Match(Pet, 'Dog', 'Cat')
Group By User;
Output in Table Box: