topic Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table in QlikView

Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

Fri, 08 May 2015 09:26:10 GMT

Hi All,

Can anyone tell me how to use Field value as Variable Name and return the Variable Value in Table? The scenario is a little bit complicated. A case is provided to explain as below, hope it could help. Thanks!

1. A source table with below structure is loaded into Qlikview:

user_id	kpi_id	Item Desc	Item	VariableName	Default Value
A	1	Notification	ntf	vK1_ntf	ON
A	1	Notification Time Interval	ntf_int	vK1_ntf_int	30
A	2	Notification	ntf	vK2_ntf	OFF
A	2	Notification Time Interval	ntf_int	vK2_ntf_int	60

2. In Variable Overview, below 4 variables are created:

vK1_ntf

vK1_ntf_int

vK2_ntf

vK2_ntf_int

- The above 4 variable names are same as the [VariableName] in the source table.

- The initial value of the above 4 variables are assigned according to the [Default Value] in the source table.

Variable Name	Value
vK1_ntf	ON
vK1_ntf_int	30
vK2_ntf	OFF
vK2_ntf_int	60

Now,

the value of these 4 variables are allowed to modified on the interface (By Input Box Object). In order to compare the [Default Value] / [New Value] of variables and exporting this table to external, a table is created and the expected result is shown as below:

user_id	kpi_id	Item Desc	Item	VariableName	Default Value	New Value	Idea to get [New Value]
A	1	Notification	ntf	vK1_ntf	ON	OFF	$(vK1_ntf)
A	1	Notification Time Interval	ntf_int	vK1_ntf_int	30	60	$(vK1_ntf_int)
A	2	Notification	ntf	vK2_ntf	OFF	ON	$(vK2_ntf)
A	2	Notification Time Interval	ntf_int	vK2_ntf_int	60	90	$(vK2_ntf_int)

My idea is to call those variables in the Expressions of the Table for each row based on the [VariableName] such that [New Value] can be returned.

However, I have no idea how to use [VariableName] to call the variables. Or can the field value ([VariableName]) be used as individual variable name and return its value?

The above scenario is also attached in QVW. Some of the method are tried but all are unexpected result. Hope if anyone can help me. Thanks in advance!

T.NG

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

sujeetsingh — Fri, 08 May 2015 09:30:33 GMT

You can use peek() function in script to calculate the variables but i doubt that it can be populated as different row values for a field. You need to implement some logic as expression and define the colum

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

sujeetsingh — Fri, 08 May 2015 09:32:09 GMT

Few questions from my side :

how you are getting the newvalue??

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

Fri, 08 May 2015 09:38:57 GMT

The new values can be modified by users on the interface (Input Box). These new value will then be displayed on pivot/straight table. By exporting this table and doing some transformation, these new values will be returned as default value next time.

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

sujeetsingh — Fri, 08 May 2015 09:41:48 GMT

What is the definition of this new variable column

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

Fri, 08 May 2015 09:44:24 GMT

Peek() should not be the case. I have already create those variables and assign the values in script, but i can't return the variable value on dashboard.

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

sujeetsingh — Fri, 08 May 2015 09:45:52 GMT

I think the only way to represent this can be the inpursum() function in the straight table.

Follow these threads

Input Fields in a straight table

How to make input box in a straight table

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

Fri, 08 May 2015 09:49:32 GMT

There is no new variable. Some variables have been created with default values. However, the value of these variables could be changed on the interface. A table is therefore required to show the old and new value.

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

swuehl — Fri, 08 May 2015 09:50:44 GMT

If this table is rather static with respect to number of variables to show, you can try a pick / match combination as expression, something like

=pick( match( VariableName, 'VK1_ntf','VK1_ntf_int','VK2_ntf','VK2_ntf_int'),

$(VK1_ntf),

$(VK1_ntf_int),

$(VK2_ntf),

$(VK2_ntf_int)

)

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

Fri, 08 May 2015 09:52:37 GMT

You need wrap around with =$(), see below:

Exp1 : =$(=$(vKPI)&$(vItem))

Exp2: =$(=$(vDynamicVariable))

Hope this was helpful or answered your question

Thanks,

Angad

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

Fri, 08 May 2015 10:07:02 GMT

your expression can return correct result when [kpi_id] and [Item] both have 1 selected value, similar to Exp5 and Exp6. however, once all selection is cleared, they return NULL value. do you have any idea?

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

Fri, 08 May 2015 10:25:07 GMT

In my case, there will be both numeric and string in column [New Value]. Can the inputfield function be used for both numeric and string?

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

Fri, 08 May 2015 10:42:05 GMT

You method could help for fixed number of variables. However, in my case, there will be hundred of variables created based on different [kpi_id] and [Item], so hope there will be another solution for this case.

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

swuehl — Fri, 08 May 2015 11:07:44 GMT

Only solution I can think of right now is enhancing my previously posted expression by building the expression in the script, so it will be kind of dynamic when adding / removing variables.

There are some older posts here in the forum that demonstrate this.

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

tahirkhalil — Thu, 26 Nov 2015 11:05:25 GMT

Hi,

Do you get any solution? I am also stuck with almost the same type of issue.

Regards

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

michael_klix — Tue, 07 Nov 2017 16:32:43 GMT

Does anyone have a solution for this problem meanwhile? I have the same issue and explain it in my words again:

A datamodel that includes a table with two columns: [KPI Name] and [comment content] (with a space, thats why brackets). This tablecontains potentially hundreds of lines like

[KPI Name] comment

KPIxyz new KPI

KPIabc hello world

...

This equals the default values described in the original article.

I am using as well an input field to allow users to edit the comment. That's why the load script reads this table, creates for any dataset a variable "KPI..." and then fills the variable with the respective comment.

result:

variable value

KPIxyz new KPI

KPIabc hello world

...

Now I have an input field for any KPI that displays the comment, and which of course can be changed (by selected users).

Next step is how to get these changed values loaded next time so that ALL users on the server see the same comments as variable changes do only happen locally.

Solution:

A streight table chart export to xls:

variable value

KPIxyz new KPI

KPIabc hello world

And the idea for the formulas of these two culumns is

Dimension: [KPI Name]

Expression: "value of the variable with the name of the field [KPI Name]"

Unfortunately things like =$(=[KPI Name] )) to fill a variable expansion with field values do not work even though mentioned here quite often.

Ideas?

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

swuehl — Tue, 07 Nov 2017 18:24:49 GMT

Hi Michael,

this thread is some years old and I hardly remember the details.

From what you've described, it seems like you are looking for some write back solution.

Maybe something like

Posting Data from QlikView to Other Systems using the Ajax Client

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

michael_klix — Tue, 07 Nov 2017 19:37:30 GMT

Hi Stefan,

Actually this would require to redesign the whole application.

The problem to be solved may serve other purposes as well however and can be reduced to the question how to call a variable while the NAME of the variable consists variably of the value of a data field.

I found descriptions like =$(=fieldname) but for some reason it does not work. Did anybody get such a construction to work in a table?

Note: =$(=fieldname) works indeed, but only if one fieldname is selected. It seems even though in a table with fieldname as dimension this formula evaluates fieldname (actually ONLY(fieldname) ) as NULL.

Von meinem iPhone gesendet

Am 07.11.2017 um 19:25 schrieb Stefan Wühl <qcwebmaster@qlikview.com<mailto:qcwebmaster@qlikview.com>>:

Qlik Community <https://community.qlik.com/?et=watches.email.thread>

Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

Antwort von Stefan Wühl<https://community.qlik.com/people/swuehl?et=watches.email.thread> in QlikView App Development - Komplette Diskussion anzeigen<https://community.qlik.com/message/1377073?et=watches.email.thread#1377073>

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

swuehl — Tue, 07 Nov 2017 20:22:57 GMT

Well not sure, if I understand what you are trying to achieve.

Could you upload a small sample QVW?

Re: Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table

marcus_sommer — Wed, 08 Nov 2017 06:43:07 GMT

Hi Michael,

AFAIK there is no way to evaluate a fieldvalue as expression or as variable unless the use of $-sign expansion which creates a further variable and which could have always only one value at a time. This meant it will only work if its unique and not on a row-level within a chart.

The only alternative is to use a pick(match()) approach like Stefan mentioned within the older part of this posting - whereby this could be created dynamically. There are already various threads with backgrounds to this topic inclusive a few examples, for example those ones:

https://community.qlik.com/search.jspa?q=variable+pick+concat+marcus

- Marcus