topic Re: Linking two fields in QlikView

Linking two fields

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have the following bar chart:

I need to augment the above chart with data from another table with information as provided in the straight table below:

The 'DMF Subject' field in the bar chart is the equivalent of the 'Active Ingredient' in the straight table above, for example the 'Pregabalin' circled in the two charts above. However, in some cases, these two field values do not match exactly in spelling. I need to be able to add a second expression to the bar chart which gives the '2015 Revenues' for the 'DMF Subject' based on the corresponding 'Active Ingredient' '2015 Revenue' from the straight table.

How can this be accomplished?

Regards

Chris

Re: Linking two fields

amayuresh — Mon, 14 Mar 2016 03:18:06 GMT

Please provide Sample Data

Re: Linking two fields

Digvijay_Singh — Mon, 14 Mar 2016 05:19:09 GMT

Try something like this, you may need two different scale to plot two measures..

Sum({<Year={2015},[DMF Subject]={"=[DMF Subject]=[Active Ingredient]">}Revenue)

Re: Linking two fields

Anonymous — Mon, 14 Mar 2016 08:11:51 GMT

Many thanks. My expression as per your recommendation is as follows:

However the chart gives the same values for the revenue plot as follows:

The revenues shown are the totals for all the products in the data set.

Regards

Re: Linking two fields

Anonymous — Mon, 14 Mar 2016 08:21:58 GMT

Dear Mauresh

Please find attached sample data. The Annual Reports Summary provides the revenue figures and the second file gives the DMF data. For the DMF data, use the 'Type' II records only.

Regards.

Re: Linking two fields

Digvijay_Singh — Mon, 14 Mar 2016 08:55:46 GMT

How are you linking both the data, is it possible to keep common name for both these fields? Currently the case is different for both the fields so they won't match. They need to be transformed to same case for comparison, lower() or Upper() function.

AFAIK if both fields have same name in data model, when you take dimension as DMF Subject then automatically it will pick revenue record from same Active ingredient.

Re: Linking two fields

Digvijay_Singh — Mon, 14 Mar 2016 12:14:35 GMT

Check if attached can help. I kept same name for subject/Ingredient.I may be wrong from my little understanding of the subject.

Re: Linking two fields

Digvijay_Singh — Mon, 14 Mar 2016 12:15:45 GMT

Oh..missed the attachment, here it is -

Re: Linking two fields

Anonymous — Tue, 15 Mar 2016 11:53:19 GMT

Many thanks, that works