Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

arixooo123
Contributor II

Using a field in both dimension and expressions

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 

 

 

 

 

 

7 Replies
MindaugasBacius
Valued Contributor III

Re: Using a field in both dimension and expressions

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
arixooo123
Contributor II

Re: Using a field in both dimension and expressions

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

I think it needs aggregations
pradosh_thakur
Honored Contributor II

Re: Using a field in both dimension and expressions

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?

Learning never stops.
MVP
MVP

Re: Using a field in both dimension and expressions

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
arixooo123
Contributor II

Re: Using a field in both dimension and expressions

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

Re: Using a field in both dimension and expressions

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 

 

arixooo123
Contributor II

Re: Using a field in both dimension and expressions

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