topic Re: Using a field in both dimension and expressions in QlikView

Using a field in both dimension and expressions

arixooo123 — Sat, 16 Nov 2024 04:29:04 GMT

This is the data I have :

ID Field Value

-------------------------------------------------

1 Fav Fruit 1 apple

1 Fav Fruit 2 orange

1 Fav Fruit 3 banana

2 Fav Fruit 1 strawberry

2 Fav Fruit 2 cherry

2 Fav Fruit 3 banana

3 Fav Fruit 1 strawberry

3 Fav Fruit 2 cherry

3 Fav Fruit 3 banana

---------------------------------------------------------------

Question : number of people having orange and banana as their second favorite fruit by their first fav fruit.

Dim :

If (Field = 'Fav Fruit 1',Value) ---> result will be apple and strawberry

now how can I count number of people having

Expression 1: count ( {<Field='Fav Fruit 2' , Value='Orange'>} ID) labeled as 2Orange

Expression 2: count ( {<Field='Fav Fruit 2' , Value='banana'>} ID) labeled as 2banana

How can I get this result?

Fav Fruite 1 Orange as 2Fav Fruit Banana as Second Fav Fruit

----------------------------------------------------------------------------------------------------------------

Apple 1 0

Strawberry 0 0

Re: Using a field in both dimension and expressions

MindaugasBacius — Tue, 12 Feb 2019 06:57:16 GMT

Expression 1: count ( {<Field={'Fav Fruit 2'} , Value={'orange'}>} ID) labeled as 2Orange

Expression 2: count ( {<Field={'Fav Fruit 2'} , Value={'banana'}>} ID) labeled as 2banana

Re: Using a field in both dimension and expressions

arixooo123 — Tue, 12 Feb 2019 07:04:40 GMT

The issue is not the missing bracket ...this is a simplified example and my data is different

I think it needs aggregations

Re: Using a field in both dimension and expressions

pradosh_thakur — Tue, 12 Feb 2019 07:45:26 GMT

Not sure i understand

"Question : number of people having orange and banana as their second favorite fruit by their first fav fruit. "

What is the expected output in tabular form?

Re: Using a field in both dimension and expressions

jonathandienst — Tue, 12 Feb 2019 07:55:30 GMT

Use [Fav Fruit 1] as the dimension of the table and ~~Count({<Fav Fruit 1] = {'orange', 'banana'}> ID)~~ Count({<Fav Fruit 2] = {'orange', 'banana'}> ID)

Re: Using a field in both dimension and expressions

arixooo123 — Tue, 12 Feb 2019 11:00:54 GMT

Thanks for the message but "Fav Fruit 2" is not a field. It's a value itself

Re: Using a field in both dimension and expressions

arixooo123 — Tue, 12 Feb 2019 11:49:37 GMT

Thanks for the reply, this is the result I want to get :

Fav Fruite 1 Orange as 2Fav Fruit Banana as Second Fav Fruit

----------------------------------------------------------------------------------------------------------------

Apple 1 0

Strawberry 0 0

I know this might seem ridiculous but it's just a simplified example of what I am dealing with

Re: Using a field in both dimension and expressions

arixooo123 — Thu, 14 Feb 2019 00:28:42 GMT

Hi Jon, did you have a chance to see my reply?

I updated the question too, I think I didn't explain what I want to achieve well