Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
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
Highlighted
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
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