topic Re: Creating a field dynamically to be used as dimension in QlikView

Creating a field dynamically to be used as dimension

Wed, 25 Sep 2013 08:49:58 GMT

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

Re: Creating a field dynamically to be used as dimension

Wed, 25 Sep 2013 09:25:16 GMT

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

Re: Creating a field dynamically to be used as dimension

Wed, 25 Sep 2013 09:37:10 GMT

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

Re: Creating a field dynamically to be used as dimension

datanibbler — Wed, 25 Sep 2013 09:43:38 GMT

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

Re: Creating a field dynamically to be used as dimension

tresB — Wed, 25 Sep 2013 09:44:37 GMT

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 <>;

Re: Creating a field dynamically to be used as dimension

v_iyyappan — Wed, 25 Sep 2013 09:46:25 GMT

Hi,

Please see the attached file,

Regards,

Re: Creating a field dynamically to be used as dimension

Wed, 25 Sep 2013 10:03:40 GMT

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

Re: Creating a field dynamically to be used as dimension

Wed, 25 Sep 2013 10:03:55 GMT

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 .

Re: Creating a field dynamically to be used as dimension

Wed, 25 Sep 2013 10:04:15 GMT

I think you missed the attachment! 🙂

Re: Creating a field dynamically to be used as dimension

Wed, 25 Sep 2013 10:05:49 GMT

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

Re: Creating a field dynamically to be used as dimension

Wed, 25 Sep 2013 10:12:54 GMT

Hi Vikram,

This helps.

So I can provide variable names in the script, allow users to make changes to the values in the dashboard and then ask user to click a button to reload the script which will refresh all objects, right?

Also, is it possible to link the button click to run only that portion of the script that creates these derived variables? I know there is a partial reload option but dont know how to specify the sections to be reloaded.

Regards,

Shweta

Re: Creating a field dynamically to be used as dimension

Wed, 25 Sep 2013 10:31:53 GMT

You can use sliders to dictate the the ranges of the Score Range. Please see the example I have attached.

This allows users to dynamically change the bounds. You could automate this further by changing the lower limit should the higher limit be dragged lower than it etc.

I hope this answers your question. Let me know how you get on.

Regards

Steve

Re: Re: Creating a field dynamically to be used as dimension

Wed, 25 Sep 2013 10:52:07 GMT

Hi Shweta,

You can reload only the specific section of your code by adding the prefix add or replace as per your needs .

Check the modified app

Re: Creating a field dynamically to be used as dimension

Fri, 27 Sep 2013 10:30:08 GMT

Thanks Vikram.

Trying this out now.

Re: Creating a field dynamically to be used as dimension

Fri, 27 Sep 2013 10:31:46 GMT

Thanks Steve.

However, I want to use the ScoreRange as dimensions elsewhere - the creation of the variables in the script itself works - refining that further.

Regards,

Shweta

Re: Re: Re: Creating a field dynamically to be used as dimension

Thu, 10 Oct 2013 09:37:06 GMT

Hi Vikram,

I'm facing a similar situation where in I'm importing data from an excel sheet. I need the calculated fields to be reloaded on partial reload based on the bounds specified but use the data residing in the Qlikview dashboard itself rather then the excel sheet.

In other words, I want the dashboard to be independent of the Excel file (the partial reload should work even if I delete/rename the excel file) from where I'm importing the original data on partial reload.

The files attached here with are little tweaked to get data from the excel file, now if I press the button after renaming the excel file, Qlikview pops-up an error.

Can you guide me, how do I go about it?

Thanks

Pawan

Re: Creating a field dynamically to be used as dimension

Tue, 15 Oct 2013 05:58:12 GMT

Hi All,

Can someone help me on this please.

I'm not able to find a solution.

Thanks