topic Re: Data island and variable in QlikView

Data island and variable

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

Hi,

I am trying to use data island to get specific text based on the value calculated in a variable.

The data island model is:

Score, Rating

1, Higher

2, Medium

3, Low

4, Moderate

11,Higher

12, Higher

...

444 Moderate

and goes up to the value 444..

I have a variable vRatingCalc which calculates the score in the score for each of the region. I would like to print the rating for the score calculated in the pivot chart.

I tried using the following and it didn't work

if ($(vRatingCalc)=Score, Rating)

I tried using set analysis but still doesn't work

maxstring({<Score="$(vRatingCalc)">}Rating)

I am not sure what I am missing here. Can you please suggest a solution?

Re: Data island and variable

vishsaggi — Tue, 30 Jan 2018 21:14:27 GMT

What is your expression in your vRatingCalc variable?

Try this?

maxstring({<Score= {$(vRatingCalc)}>}Rating)

Re: Data island and variable

SureshUK — Tue, 30 Jan 2018 21:28:55 GMT

This doesn't work. The variable vRatingCalc returns a number between 1 and 444.

Re: Data island and variable

vishsaggi — Tue, 30 Jan 2018 21:33:23 GMT

Ok can you answer my first question please. Can i know the expr you are using in your vRatingCalc variable?

Can you share some sample data? Where are you using this If() condition and your maxstring(), i mean are you using in any chart or tables?

Re: Data island and variable

SureshUK — Tue, 30 Jan 2018 21:40:01 GMT

The vRating is an expression formed by concatenating regional rating by using concat and aggr. The resultant is a numeric four digit value.

I am using this in the pivot chart.

Re: Data island and variable

zebhashmi — Tue, 30 Jan 2018 21:41:40 GMT

vRatingCalc = 12

if(vRatingCalc = Score,Rating)

Re: Data island and variable

vishsaggi — Tue, 30 Jan 2018 21:51:28 GMT

Did you try? Can you check what value are you getting for vRatingCalc variable if you put this variable in a textbox.

Check below:

= IF(Score = vRatingCalc, Rating)

Re: Data island and variable

SureshUK — Tue, 30 Jan 2018 21:57:57 GMT

I tried that and it prints a number 114. I am sure that the matching row exists in island table too. I even tried storing the value in a column and used the column(1) instead and even that doesn't return any value!

Re: Data island and variable

vishsaggi — Tue, 30 Jan 2018 22:00:42 GMT

Can you share a sample to look into please...

Re: Data island and variable

SureshUK — Tue, 30 Jan 2018 22:11:10 GMT

Quickly created a sample file. The chart titled "Derived Score". The Final Rating column is displayed as Null.

Re: Data island and variable

vishsaggi — Tue, 30 Jan 2018 22:37:50 GMT

Try this in your Final Rating

= Aggr(IF(Match(Score, $(vVal)), Rating), Score, Code)

Re: Data island and variable

SureshUK — Wed, 31 Jan 2018 09:47:41 GMT

Thanks Vishwarath. This works when I use a simple variable. My variable has aggregation function so it fails for me. Do you know how to make it work by referencing a another column value on the same chart? When I tried

=Aggr(IF (Match(Score,Test)),Rating), Score, Code) it fails. Test has an expression "= $(vVal)"

Re: Data island and variable

vishsaggi — Wed, 31 Jan 2018 14:53:30 GMT

Is Test your variable? You may want to use $(Test) ?

Re: Data island and variable

SureshUK — Wed, 31 Jan 2018 15:08:16 GMT

Test is an expression name in the chart.