Set_expression for selecting a variable from an Excel file

[datanibbler]

Hi,

I have a number of QlikView_Dashboards where the KPIs of the business are displayed.

All (most of them) have a target value, defined by ofur management, which can be used to display a status (green or red).

These target values are dynamically drawn from an Excel_file. I have a code (loop) to generate a nr. of variables from that file.

Starting this year, some of the target_values are split between the two plants we have.

=> Say, I have two target_values for internal damages, one for each plant - is there any way I can give those two values the same name in the Excel_file (which would make it easier to look for it using a custom_filter) and then use another field to decide which plant that value is for - using a set_expression or something?

I have tried, but it's not so easy as the target_value is a variable, so I cannot use sum() or some other aggregation fct., which is a prerequisite for set_analysis.

Can anyone give me a hint how to do this?

Thanks a lot!

Best regards,

DataNibbler

[#2]

If you are setting a variable, can you not just create an inline table and pull through both values and manually assign them to a specific plant?

That way you can use the variable in your analysis and then use set analysis to set which plant you are looking at.

Something like:

Load * Inline

Plant, Variable

Plant1, $(Variable1)

Plant2, $(Variable2)

];

Edit: Removed square brackets

[datanibbler]

Hi Tyler,

of course in an inline table it would be much easier - for me.

That, however, is just the point: The plant managers should have the target_values in their own sphere of influence - it's also a matter of consolidating things, to reduce both the potential for errors and - last, but not least - workload 😉

Best regards,

DataNibbler

[#4]

Hi DN,

Does this mean that you are after a value that can be updated in-app rather than simply off the load from the excel file?

Otherwise I don't quite see why you don't want to use a table to assign the values rather than a variable. The inline table is calculated using the variables loaded, it just makes it easier to reference in the front end.

Cheers,

Tyler

[datanibbler]

Hi Tyler,

no - the values are and should be loaded from an Excel file.

There is only the "detour" of generating a number of variables from that Excel_file (after loading the relevant part of it) and deleting the table.

That detour is something I could think about abandoning altogether if there is no way - or at least no elegant way - of separating that - but that would be quite some effort. That other field denoting the "validity in plant" would be like an attribute of the variable - I'd have two variables with the same name, but different "validity attributes" and I would need a way of using that attribute in a Dashboard to decide which of the two variables to pick.

True, it is not very much work to just look a bit more closely at the custom_filter in the Excel_file (to name the variables differently) - it will most probably be myself maintaining that file anyway - management could do it, they have the access, but I guess that's not their idea of what a manager should be doing ...

[#6]

Hi DN,

Made a mistake with the inline table actually sorry, you don't want the square brackets. I think that I am not explaining my concept particularly well since from what you are describing, it is exactly what you want. Just test this script and see what it does, hopefully it gives you the idea of what I am suggesting.

Let v1 = 'Test1';

Let v2 = 'Test2';

Test:

Load * Inline [

ID, Value

1, $(v1)

2, $(v2)

];

Cheers,

Tyler

[jzimolong]

Could you provide any sampling of the code used currently to set variables?  Also, what the Excel file looks like that you need to interrogate?  I take it you set these variables during the LOAD process, or do you load the Excel file, then kick off the macro (vbscript?) once the data is loaded?

[datanibbler]

Hi Joseph,

there is no macro. It is a piece of code in the script - so yes, in a way it is in the LOAD process - first, the Excel_file is loaded and then it is parsed line by line in a LOOP to create the variables - the name of the variables and their value are both taken from the Excel_file.

Pretty straightforward actually - but with variables you cannot use set_expressions.

Well, then I guess I'll have to do with an additional filter in the LOAD of the Excel_file so only the variables for the specific plant are loaded.

Best regards,

DataNibbler