Creating a field dynamically to be used as dimension

[#1]

Hi,

I need to create a field (~ additional column in the data) based on inputs given by the user in Qlikview and then use this derived field as a dimension.

Ex. My input data has

Company	Score
1	10
2	17
3	4
4	20
5	8
6	2

Now the user will input a range to classify the scores. Ex. 2 cutoffs as 5 and 15. Based on this, every company will be classified as low, middle or high and I want to then obtain charts such as sales by Score Range, export data of all companies in a particular Score Range. Because these score ranges are dynamic, I cannot define these in the input data. So, is it possible for Qlikview to prepare this new column internally so that I can then use it as a dimension.

Company	Score	Score Range (Based on 5 and 15) (QV to prepare dynamically)
1	10	Middle
2	17	High
3	4	Low
4	20	High
5	8	Middle
6	2	Low

To generalize, I need to create a new dimension in Qlikview which will be defined as an expression (not as simple as the one above).

Any help appreciated.

Shweta

[#2]

hi,

In your chart, add a calculated dimension, something like:

= if(Score<$(vc1), 'LOW', if(Score < $(vc2), 'MEDIUM', 'HIGH'))

where vc1 and vc2 are variables.

Remove the dimension Customer (it is now calculated) if previously in your chart.

Fabrice

[#3]

Thanks Fabrice.

I want this calculated dimension to be available across other components as well and not only in the chart, like tables. Is there a way I can do that.

Shweta

[datanibbler]

Hi Svetlana,

you can store an expression in a variable as well as a value.

Alternatively, you could create that calculated dimension as a native field in the script. Variables can be used there - provided they are already available, thus they have to be created earlier in the script.

HTH

Best regards,

DataNibbler

[tresesco]

Hello Shweta,

The fields which are created in the script can be used across the application whereas the calculated field (rather dimension) ceated in a chart can't be used/referred in other object. Therefore, create a field in script like:

Load  Company,

          Score,

          If(Score>=15, 'High', If(Score>=10, 'Medium' , 'Low')) as Grade

From <>;

[v_iyyappan]

Hi,

Please see the attached file,

Regards,

[#7]

Hi Tresesco,

The cutoffs of 15 and 10 are user inputs in the dashboard and hence I cannot specify these in the data import script.

Is there a way to define the fields after the data import?

Regards,

Shweta

[#8]

Hi Svetlana,

Have a look at the attached example. Unless and until you reload the application you cant send the input from users to backend .

[#9]

I think you missed the attachment! 🙂

[#10]

Hi DataNibbler,

The input parameters in this case the cutoffs for high and low are dynamic and the user inputs them in the dashboard - hence cant include in the scipt.

Storing an expression in a variable will apply to only that component, right? I want it to be available to all objects on the sheet.

Regards,

Svetlana