topic Re: Assigning Expressions within the load script in QlikView

Assigning Expressions within the load script

richard_pearce6 — Fri, 25 Jul 2014 13:51:34 GMT

Hi,

We have a requirement where a number of metrics defined within the load script have their own expression. For example:

Measure ,Expression

Total ,Sum(Sales)

Average ,Avg(Sales)

We've created a chart where the Measure is the dimension and the expression is =only(Expression)

I've tried various combinations of dollar expansion and not been able to get it working.

Can someone provide assistance???

Thanks

Richard

Re: Assigning Expressions within the load script

Fri, 25 Jul 2014 13:54:13 GMT

Are you able to provide an example?

Re: Assigning Expressions within the load script

richard_pearce6 — Fri, 25 Jul 2014 13:59:17 GMT

Example attached

Re: Assigning Expressions within the load script

stigchel — Fri, 25 Jul 2014 14:00:25 GMT

I have used this successfully, first of all you need to make sure that only 1 value in Expression is valid and then use this as expression:

=$(=Expression)

Mind the $(=

Re: Assigning Expressions within the load script

richard_pearce6 — Fri, 25 Jul 2014 14:06:10 GMT

Hi,

I've used this before with a single expression although wanted to be able to use several. Have you managed to do this type of thing before?

Really its the same as using the expression below although we want to make it feed automatically from the database (as field expression). I could create a script that creates the expression varaible as per below but wondered if there was a better way?

(Measure='Average',avg(Sales)
,if(Measure='Total',Sum(Sales)))

Re: Assigning Expressions within the load script

Fri, 25 Jul 2014 14:11:16 GMT

Not sure if you can it this way with multiple expressions like that, if I was going to do something like this I would use a combination of pick and match functions instead of an If statement.

=pick(match(Measure, 'Average','Total'),Avg(Sales),Sum(Sales))

Re: Assigning Expressions within the load script

stigchel — Fri, 25 Jul 2014 14:16:22 GMT

Yes sorry, I typed it before you attached the example. I used it for a single expression as well. The problem here is that the dollar sign expansion is only assigned once for the whole chart and not per dimension value. Without it the expression is not evaluated

Re: Assigning Expressions within the load script

richard_pearce6 — Fri, 25 Jul 2014 14:16:48 GMT

I wasn't sure either Joe. My alternative works although Pick and Match may be a faster calculation time... Alternativly I may be able to pre-calculate in the script depending on other requirements.....

If anyone has made this work please let me know. Or, if you know the technical QlikView reason wy not I'd be interested in knowing that also.

Cheers

Re: Assigning Expressions within the load script

martynlloyd — Fri, 25 Jul 2014 14:22:02 GMT

Why not use conditional expressions, based on a variable vMeasure?

It is how Qlik do it in their What's New in QlikView 11 example app...

K.I.S.S.

Regards.

Re: Assigning Expressions within the load script

richard_pearce6 — Fri, 25 Jul 2014 14:25:10 GMT

Ah that makes perfect sense. !! So it's calculated as a null value which is exactly what I am seeing. I honestly didn't think it was possible but couldn't think of the technical reason (and sometimes you never know, it may have been possible)..

Cheers !!!!

Re: Assigning Expressions within the load script

richard_pearce6 — Fri, 25 Jul 2014 14:30:39 GMT

Good thought Martyn (hope you're not calling me S? )

Thinking about the table though both expressions would be true and wouldn't show / hide depending on the dimension and I'd have more than one column unless I'm missing something?

Also, I need to make it dynamic as the calulations may change or more could be added.

Rich

Re: Assigning Expressions within the load script

martynlloyd — Fri, 25 Jul 2014 14:42:54 GMT

No way my Sage!

I was assuming that measure would be selected somewhere else, possibly as an 'always select one value' listbox

If that is not the case, then your chosen method is the way to go.

I use this technique to create an interactive report builder for users; we now have Sense (c) of course... lol.

Regards,

Marty.

Re: Re: Assigning Expressions within the load script

Fri, 25 Jul 2014 14:44:09 GMT

This isn't pretty, but if you can get your measure and expression loaded in a different way, should be able to use the fields dynamically within your pick and match.

Just food for thought really

Re: Assigning Expressions within the load script

richard_pearce6 — Fri, 25 Jul 2014 14:46:21 GMT

Yeh, I use a similar method with FirstSortedValue and a data island to do charts like that. Its a very handy technique.

Thanks for your input Martyn, muchly appreciated !

Rich

Re: Re: Assigning Expressions within the load script

jonathandienst — Fri, 25 Jul 2014 14:55:53 GMT

No problem to do dynamically, just build a Pick(Match()) expression in the script:

Expression:

Load * Inline [

Measure ,Expression

Total ,Sum(Sales)

Average ,Avg(Sales)

];

Concat:

LOAD

chr(39) & Concat(Measure, ',') & chr(39) As cLines,

Concat(Expression, ',') As cExpr

Resident Expression;

Let vLines = Replace(Peek('cLines'), ',', chr(39) & ',' & chr(39));

Let vExpr = Peek('cExpr');

Set vPick = Pick(Match(Measure, $(vLines)), $(vExpr));

DROP Table Concat;

Now in a table with dimension [Measure], use the expression: =$(vPick)

See attached.

HTH

Jonathan

Re: Re: Assigning Expressions within the load script

richard_pearce6 — Fri, 25 Jul 2014 14:59:06 GMT

Thanks for the example Jonathan. !!!

I'll have a look at this in the QV, cheers.

Rich