topic Re: Formula/Variable in Load*Inline in QlikView

Formula/Variable in Load*Inline

Fri, 16 May 2014 13:32:00 GMT

I want to load a formula (or variable containing a formula) in a load*inline.

As a simple example I got:

load * inline [

Variable_name, sum

x, sum({<ProdType={'PS-E'}>}ActQuantity)

y, 100

]

When I make a table box (or a graph) it does not return me the actual value for the sum just the text.

Any suggestions?

Re: Formula/Variable in Load*Inline

Anonymous — Fri, 16 May 2014 13:34:06 GMT

Jarl

Have you used a dollar expansion when you use it ?

Re: Formula/Variable in Load*Inline

Fri, 16 May 2014 13:35:41 GMT

if you want to create a variable with a formula use

SET varX = SUM({<ProdType={'PS-E'}>}ActQuantity)

Re: Formula/Variable in Load*Inline

jonathandienst — Fri, 16 May 2014 13:37:31 GMT

The inline statement looks OK to me, I suggest that you either explain how you are using these fields, or better yet, post a sample.

Jonathan

Re: Formula/Variable in Load*Inline

Fri, 16 May 2014 13:54:09 GMT

Hi Felim, Jonathan and Bill. Thank you for your response and I am afraid I haven't figured it out yet.

I am trying to create a new variable for a dimension on a bar graph which only contains a few restricted sums.

I have added a picture of a table box on the left with the Load Inline, and the actual calculation.

So what I expected to see is instead of the text: " SUM ... i want to see the number 1044..

So I tried using a dollar sign so $(SUM({<ProdType={'Meat'}>}ActQuantity)) but it returns a null value .

Re: Formula/Variable in Load*Inline

jonathandienst — Fri, 16 May 2014 13:59:19 GMT

Not sure if you mean the caption or the column header, but you should just use

=SUM({<ProdType={'Meat'}>} ActQuantity)

HTH

Jonathan

Re: Formula/Variable in Load*Inline

Fri, 16 May 2014 14:04:06 GMT

Well the caption doesnt really matter. So I added the sign as well as in my load inline code:

load * inline [
Variable_name, sum
x, =SUM({<ProdType={'Meat'}>} ActQuantity)
y, 100
]

Also if I added a dollar sign infront of it: $(=SUM({<ProdType={'Meat'}>} ActQuantity)). It gave me a null value

Re: Formula/Variable in Load*Inline

Fri, 16 May 2014 14:17:14 GMT

If you create the variable mentioned above use =$(varX)

Re: Formula/Variable in Load*Inline

Fri, 16 May 2014 14:32:11 GMT

So I got:

set varX = SUM({<ProdType={'Meat'}>} ActQuantity);
load * inline [
Variable_name, sum
x, =$(varX)
y, 100
]

And it still give me the text instead of the actual value:

Weird right.

Re: Formula/Variable in Load*Inline

Fri, 16 May 2014 14:55:21 GMT

//Test Load for expression values

test1:

Load * inline [

ProdType, ActQuantity

PS-E, 100

];

// Varaible Load

test:

NoConcatenate

load * inline [

ID, Variable_name, sum

1, x,sum({<ProdType={'PS-E'}>}ActQuantity)

2, y, 100

];

//Concatenating the expressions

ConcatExpressions:

LOAD Concat(sum, ',', ID) As ConcatExpression

Resident test;

//Variable used in Expression

Let vConcatExpressions = 'Pick(ID,' & Peek('ConcatExpression') & ')';

Create a straight table chart, Have dimension as Variable_name and expression as $(vConcatExpressions).

Hope it helps and works for your use case.

Re: Re: Formula/Variable in Load*Inline

Anonymous — Fri, 16 May 2014 14:55:49 GMT

Jarl

I am pretty sure you need to use dollar expansion in the expression in your Chart Table.

For a test try putting the below in as the expression in a text object.

=$(=1+1)

I often find it best to test syntax in a simple text box.

Do note the second = is needed. If in the text object you remove this second = you will see that breaks it.

Re: Formula/Variable in Load*Inline

Fri, 16 May 2014 15:17:50 GMT

Thanks all for your help! Got a big smile on my face, great way to start the long weekend. Got into Qlikview last week and it is challenging but I love it! The community is also great. Keep up the good work Bill, Sudheer, Felim and Jonathan.

Re: Formula/Variable in Load*Inline

Anonymous — Fri, 16 May 2014 17:35:36 GMT

Thank you for the compliment and for marking the helpful and correct answers for other members. Have a great weekend.

Sara