Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

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 

 

 

 

 

 

Labels (3)
7 Replies
Highlighted
Specialist III
Specialist III

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
Highlighted
Creator III
Creator III

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

I think it needs aggregations
Highlighted
Master II
Master II

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.
Highlighted

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
Creator III
Creator III

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

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 

 

Highlighted
Creator III
Creator III

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