Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Hoowling
Contributor III
Contributor III

Lookup field value within field

Hi qlik community!, 

I have a data table that entails information about listed companies. Given my selection of company I want to show all the peers of the selected company. The data is organized as Company, KPI, Value. "Company sector" is listed under KPI and for one particular company the sector could by "Consumables". 

If I select a company in the consumables sector I want to show all other companies within the same sector. I would also like to calculate industry averages given the selection - e.g. profitability is listed under KPI aswell, so given that I select a company in the consumables sector, I would like to show the average profitability across that sector. What I'm looking for basically is a lookup function for the company sector field value for another field value within the same field (hopefully not too confusing).

It would be much appreciated if someone could provide me with a solution to this problem - Ï've been struggling with it for some time....

Thanks!

Labels (4)
1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

Hi,

You can use P() function in set analysis to return the possible set based on the selection, something like the below;

20190212_1.png

Hope that helps.

Cheers,

Chris.

View solution in original post

4 Replies
chrismarlow
Specialist II
Specialist II

Hi,

You can use P() function in set analysis to return the possible set based on the selection, something like the below;

20190212_1.png

Hope that helps.

Cheers,

Chris.

View solution in original post

Hoowling
Contributor III
Contributor III
Author

Thanks for the quick response. 

This is what I'm looking for. The problem however is that Sector is not a dimension - it is a field under KPI (which is a dimension) - see picture below. How would you write the formula given that? Choosing AarhusKarlshamn below to return all companies (bolag) for KPI = "info sektor" with Data_nyckeltal = Dagligvaror.

Snipaste_2019-02-12_21-32-12.png

 

chrismarlow
Specialist II
Specialist II

Hi,

I would be inclined to remodel your data, but the following might work if that is not an option;

20190212_2.png

Only({<Company=P({1<KPI={'Sector'},Data=P({<KPI={'Sector'}>} Data)>} Company)>}Data)

With field names amended as you need.

Cheers,

Chris.

Hoowling
Contributor III
Contributor III
Author

I remodeled my data as per your advice and the P() function works perfectly - Thanks!