topic Re: Calculated column based on value in another field in QlikView

Calculated column based on value in another field

nigel987 — Mon, 21 Sep 2015 13:31:57 GMT

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:

Re: Calculated column based on value in another field

alexandros17 — Mon, 21 Sep 2015 13:39:48 GMT

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;

Re: Calculated column based on value in another field

nigel987 — Mon, 21 Sep 2015 13:48:08 GMT

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

Re: Calculated column based on value in another field

pamaxeed — Mon, 21 Sep 2015 13:56:35 GMT

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;

Re: Calculated column based on value in another field

sunny_talwar — Mon, 21 Sep 2015 13:57:55 GMT

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;

Re: Calculated column based on value in another field

alexandros17 — Mon, 21 Sep 2015 14:11:42 GMT

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;

Re: Calculated column based on value in another field

sunny_talwar — Mon, 21 Sep 2015 14:20:55 GMT

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: