topic Re: How to execute variable expansion on inline table in QlikView

How to execute variable expansion on inline table

Fri, 07 Dec 2012 22:17:38 GMT

We use ($) to expand our variables in our objects. I'm trying to do the same thing but with a field value that I created in an inline table.

It's a cool trick I use for sorting pivot tables, I usually use buttons with variables and then use the $(variable) expansion in the sort expression but

in this case I have so many it would look nicer if I allowed the users to select using a list box.

So for example this simple inline table

LOAD * INLINE [A,B

ValueA, =sum({$<[SourceName] = {'CurrentView'}>}[OutlookUnits])];

I want the value for B (=sum({$<[SourceName] = {'CurrentView'}>}[OutlookUnits]) to be executed when "ValueA" is selected. I usually use variables for this in my pivot table and dollar sign expansion, but $(B) doesn't work.

Hope that makes sense. Thanks for anything.

Re: How to execute variable expansion on inline table

Gysbert_Wassenaar — Sat, 08 Dec 2012 07:53:42 GMT

Try $(=B). See attached example

Re: How to execute variable expansion on inline table

Mon, 10 Dec 2012 17:13:24 GMT

Gysbert - You have been a life saver and I very much appreciate the help you have been providing. I knew it was something simple like that! Please see attachment though, this ties to another post you commented on last week. Let's say in your inline table you want to add a more complex set expression like I did called "SumHELP" as the Label. You see how I have used a ',' in my expression. I have tried replacing with chr(44) and using double quotes but still can't get the "Expression" record to include the comma. Qlikview thinks the comma is the end of the line and it just truncates the rest of my set expression.

You have a fancy work-around for that? Thanks again for everything

Re: How to execute variable expansion on inline table

Gysbert_Wassenaar — Mon, 10 Dec 2012 17:22:26 GMT

You can use a different separator for the inline. Like '@' below, or tabs with (delimiter is '\t').

MyExpressions:

load * Inline [

Label@ Expression

Sum2008@ sum({<Year={2008}>}Sales)

Sum2009@ sum({<Year={2009}>}Sales)

Sum2010@ sum({<Year={2010}>}Sales)

SumHELP@ sum({<Year={2008},Region = {'England','N Ireland'}>}Sales)

] (delimiter is '@');

Re: How to execute variable expansion on inline table

Mon, 10 Dec 2012 19:13:58 GMT

Gysbert right again. Thank you for the explanation of inline delimiters! This works great but of course I have one more challenge. See attachment. In one of my set analysis expressions I use a dollar sign expansion. Qlikview is attempting to expand when it builds the table vs just adding the literal text. I looked at your previous responses to this delima but still can't nail it. As an idea I added another table which sources the inline table but uses the function REPLACE to replace the string ... in both scenario's I get a "internal error" where Qlikview is expanding my text.

Suggestions? Thanks Gysbert

Re: How to execute variable expansion on inline table

Gysbert_Wassenaar — Tue, 11 Dec 2012 16:37:21 GMT

Use ~ (or another character of your choice) instead of $ in the expression you want to load, then use Replace(Expression,'~','$') to fix the expressions so the $ is put in the right places in the expressions.

Re: How to execute variable expansion on inline table

Tue, 11 Dec 2012 16:48:39 GMT

Gysbert - Thanks again. So I was doing the right thing I just tried to get too fancy and use the REPLACE function for more than just the dollar sign. It works! Thanks again ... hopefully some day I can help you when you are stuck.

Re: How to execute variable expansion on inline table

Fri, 03 Mar 2017 10:21:27 GMT

Hi Gysbert, I'm wondering why this doesn't work, if no Label is selected (or more than one selected). Do you have any Idea how I could work around it? I just need a table with 2 dimensions Label and Product and the same Expression as already exists. Thanks, Hamid