topic Re: Using expression in Inline table in QlikView

Using expression in Inline table

jimgibson — Wed, 25 Nov 2020 16:16:04 GMT

I am loading the following INLINE table

LOAD * INLINE [
Metric, ShortDSC, LongDSC, SortOrder, MedianCalc
A-AD, Door to Admit, median of Door to Admit, 1, Median('A-AD')
A-OF, Door to On-Floor, median of On-floor, 3, Median('A-OF')
A-RD, Door to Ready for Discharge, median door to discharge, 2, Median('A-RD')
];

This loads without errors but when I try to load the Expression into the Definition box of a straight table as $(MedianCalc) I do not get any results. Normally to use the expression I have to use Median([A-AD]) however the INLINE does not like the [] so I replaced them with '' and it loads.

Any ideas on what I am doing wrong?

Re: Using expression in Inline table

jonathandienst — Tue, 07 Jun 2016 06:47:09 GMT

You can replace the square brackets on the inline using double quotes. Then you can use square brackets in the inline.

LOAD * Inline

...

Re: Using expression in Inline table

jonathandienst — Tue, 07 Jun 2016 06:52:30 GMT

Are you trying to reference the field in the $ expansion? Do you want to evaluate it as an expression? is A-AD a field in your model?

I am not sure what you mean by the "definition box".

Re: Using expression in Inline table

Peter_Cammaert — Tue, 07 Jun 2016 06:58:38 GMT

Also note that in a straight table this will only work if you don't use any of the other INLINE table field as a dimension. $-sign substitution will be performed only once for the entire table, not row-by-row.

You can use the Metric field as a list box with Always-One-Selected enabled, to select a single expression to be displayed.

Re: Using expression in Inline table

settu_periasamy — Tue, 07 Jun 2016 07:12:19 GMT

Hi,

May be try =$(=MedianCalc)

Re: Using expression in Inline table

Peter_Cammaert — Tue, 07 Jun 2016 07:29:36 GMT

IMHO that won't work with MedianCalc being a field name. Because as a first step the expression will expand to:

=$(Median([A-AD])) // doesn't work as there is no variable with that name

if a single Metric is active, or to

=$() // probably generates an error

if multiple Metrics are selected.

Jim will have to use an intermediate variable that first upon evaluation stores the value of MedianCalc and only then does the $-sign substitution.

In Variable Overview, define a variable called vMetricExpression and assign the following as value:

=MedianCalc

Then in the expression field where the dynamic expression should provide results, put:

=$(vMetricExpression)

Best,

Peter

Re: Using expression in Inline table

settu_periasamy — Tue, 07 Jun 2016 08:00:19 GMT

Thank you Peter ..

Re: Using expression in Inline table

jimgibson — Tue, 07 Jun 2016 14:04:06 GMT

Peter,

I tried this but I still do not get any results. See the attached PDF. The third column in the straight table does not give any results.

Any ideas?

Re: Using expression in Inline table

settu_periasamy — Tue, 07 Jun 2016 14:07:21 GMT

Can you share the Sample Qvw?

Re: Using expression in Inline table

ramoncova06 — Tue, 07 Jun 2016 14:14:16 GMT

if you say that the expression works ok with brackets, then just replace where you add the brackets to the field, you can do this with a preceding load

load

Metric,

ShortDSC,

LongDSC,

SortOrder,

'Median(['&MedianCalc&'])' as MedianCalc;

LOAD * INLINE [

Metric, ShortDSC, LongDSC, SortOrder, MedianCalc

A-AD, Door to Admit, median of Door to Admit, 1, A-AD

A-OF, Door to On-Floor, median of On-floor, 3, A-OF

A-RD, Door to Ready for Discharge, median door to discharge, 2, A-RD

];

Re: Using expression in Inline table

ramoncova06 — Tue, 07 Jun 2016 14:44:16 GMT

would need to look you dataset, but this seems to work ok for me

Re: Using expression in Inline table

jimgibson — Tue, 07 Jun 2016 15:31:21 GMT

A-AD is not the field A minus the Field AD. There is a column in the data table I load from SQL called "A-AD". It is that field that I am trying to find the median of.

Re: Using expression in Inline table

ramoncova06 — Tue, 07 Jun 2016 15:55:05 GMT

this should work then, though as Peter already mentioned you need to ensure one of the metrics is always selected

Re: Using expression in Inline table

jimgibson — Tue, 07 Jun 2016 16:11:16 GMT

Here is a sample qvw. Hopefully this will make what I am doing more understandable

Re: Using expression in Inline table

jimgibson — Tue, 07 Jun 2016 16:16:16 GMT

Ramon,

I have sent a sample .qvw in a separate post.

Re: Using expression in Inline table

settu_periasamy — Tue, 07 Jun 2016 17:16:11 GMT

Hi Jim,

Try this expression..

$(=concat('if(Metric=' & chr(39) & Metric & chr(39) & ',Median({1}[' & Metric & '])', ',') & concat(right(Metric&')',1)))

Re: Using expression in Inline table

Peter_Cammaert — Tue, 07 Jun 2016 17:31:56 GMT

Very nice, and this is about the only solution to get dynamically changing expressions in a straight table.

I was going to say that your last resort would be to create a big nested IF() that tests dimension values and executes the corresponding expression. But that would be a rigid solution. Every time a new metric is added, you would have to add an expression to the IF() construct. Settu's technique avoids that problem and operates from the entire INLINE table.

This trick was invented by John W.‌ if I'm not mistaken. Store it in a safe place for whenever you need it again.

Peter

[Edit] So I was mistaken. rbecher created this technique. Credit where it's due!

Re: Using expression in Inline table

settu_periasamy — Tue, 07 Jun 2016 18:00:36 GMT

Yes peter.. i was trying to provide the discussion link, but not able to find it..

Edit: I think this is the one..

Chart with count of $Field values

Re: Using expression in Inline table

jimgibson — Tue, 07 Jun 2016 20:09:12 GMT

This works and I have validated the Medians. Thank you.

One problem and I am not sure this is possible. It comes up correctly the first time but as you change the items in your selected fields the numbers in the table do not change.

Re: Using expression in Inline table

Peter_Cammaert — Tue, 07 Jun 2016 20:31:15 GMT

Remove the {1} set specification from settu's expression and try again. Base set 1 means use all data and ignore all selections.