Skip to main content
Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for 
Search instead for 
Did you mean: 
nigel987
Creator II
Creator II

Calculated column based on value in another field

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:

1 Solution

Accepted Solutions
alexandros17
Partner - Champion III
Partner - Champion III

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;

View solution in original post

6 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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;

nigel987
Creator II
Creator II
Author

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

pamaxeed
Partner - Creator III
Partner - Creator III

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;

sunny_talwar

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;

alexandros17
Partner - Champion III
Partner - Champion III

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;

sunny_talwar

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:

Capture.PNG