topic Use Set Analysis as reference to a variable in QlikView

Use Set Analysis as reference to a variable

SupplyAndDemand — Sat, 16 Nov 2024 03:59:05 GMT

I am creating a QlikView template for my App that can be controlled from the script.

In the script I have a table with a referance to different colours and other attributes for each sheet. I am not very familiar with referencing variables and I struggle to reference a variable from the table into a set analysis, which again is supposed to be interpreted as a variable to change the colour of the background of the sheets.

Can anyone please help me to create the Set Variable and the syntax for referencing it in the colour of a textbox?

// Define my set of colours
Let vColour1				= rgb(248,248,248);
Let vColour2				= rgb(39,174,96);

Define the parameters for each sheet
SheetDesignParameters:
LOAD*INLINE
[
sheet_id,	sheet_title,	sheet_colour_background,
Document\SH01,	Overview,	vColour4,
Document\SH02,	12-Cell,	vColour4,
];

// Returns the active sheet ID
Set vActiveSheetID = GetActiveSheetId();
// Returns the row number of the sheet-id from the array  
Set vFieldIndex = FieldIndex('sheet_id',$(vActiveSheetID));

//first try, but does not work because the values in the table are not unique 
Set vSheetColourBackground = FieldValue('sheet_colour_background', $(vFieldIndex));

// second try, shows the string "vColour4" if I put the code directly in a textbox in the first sheet, but does not change colour.
Set TestTwo =Only({$<sheet_id= {'$(=$(vActiveSheetID))' }>}sheet_colour_background);

Re: Use Set Analysis as reference to a variable

marcus_sommer — Tue, 02 Apr 2019 15:00:44 GMT

I think there are too much variables. This doesn't mean that there is no way to get it to work with a whole bunch of variables but IMO it doesn't simplify the task (which is the main-use of variables) else it makes it more complex. Therefore I suggest to load all parameters in a table by formatting the rgb() with num() to get the color-index of your needed colors and then using them instead of the rgb() which is without a $-sign expansion just a string. This means something like:

LOAD *, num(evaluate(sheet_colour_background)) as ColorIndex INLINE [
sheet_id, sheet_title, sheet_colour_background
Document\SH01, Overview, "rgb(248,248,248)"
Document\SH02, 12-Cell, "rgb(39,174,96)"];

Only({$<sheet_id= {'$(=GetActiveSheetID())'}>} ColorIndex)

and based of it it might be useful to create some kind of custom function with:

varReturn: Only({$<sheet_id= {'$(=GetActiveSheetID())'}>} $1)

$(varReturn(ColorIndex))

to choose the returning field.

- Marcus

Re: Use Set Analysis as reference to a variable

SupplyAndDemand — Mon, 08 Apr 2019 16:07:07 GMT

Thank you Marcus, the evaluate did work and I can now show my colors from the table (did not have to write the rgb(), could just reference to vColour1). I am not an advanced user, so I dont know how to create the custom function you mentioned. I ended up putting the expression into all the objects manually on the main page. (This main page should be copied and pasted as a link to create more pages). The only important thing missing is now to get the title of the sheets on the change tab buttons instead of the document ID, and I cant seem to get that to work. Do you have any suggestions?

I attached the project I am working on, so you can better understand what I am trying to do. (Also if you have other comments, I would appreciate the feedback).

Re: Use Set Analysis as reference to a variable

marcus_sommer — Wed, 03 Apr 2019 13:35:39 GMT

The custom function could be easily defined within the variable-editor. It's also possible within the script but it need more or less expensive adjustments in regard to quotes and used $-signs - usually it's not worth to go this way and should really various of such variables be created within the script a load of them from an external source would be the better approach.

In regard to your button-labels - you need just define them within your table like all the other id's, colors and so on.

Beside this I don't recommend such an approach to create an UI because the efforts to create all these variables, buttons and textboxes and implement it everywhere is enormously and IMO without a real benefit. Especially as a starter in Qlik it could cost weeks or even months to implement here a stable and flexible solution.

Why not just using the "normal" way by using the common sheet-tabs, listboxes and so on. Most of the functionalities and layouts could be reached with it. If templates for sheets and/or objects are needed you could use themes to define various colors and other properties - whereby even this might not be necessary. Usually I use just a copy + paste approach to duplicate applications, sheets and objects and if necessary I adjust them to the new requirement.

Nevertheless if you want to continue with your approach I suggest to read Variables.

- Marcus

Re: Use Set Analysis as reference to a variable

SupplyAndDemand — Fri, 05 Apr 2019 07:10:19 GMT

You are right, I lost my perspective trying to make this task work for so long. After your suggestion I took a step back and realized I was almost trying to recreate the tab row. It was hard to delete, but I ended up removing all the sheet specific variables and the change tab button. I replaced them with a show/hide and a color variable per section. Looks pretty good and are actually more flexible and easy then my previous version. Thank you for your time!