topic Re: Error with expression in QlikView

Error with expression

Fri, 16 Oct 2015 07:33:03 GMT

Hi,

A solution was kindly provided to us via the community which when selected just brough through a certain amount of properties meeting the following criteria:

This is on a serach string on a button entitled red properties. The criteria for red is any property with a value less than 4999:

concat({$ <[NPV Total]={"<-£4999"}>} Address, '"' & '|' & '"') & '"' & ')'

It worked to date but now with new data its showing one property as green when the button is selected. Please see attachment on what happens and then how its resolved - but we shouldnt have to additional bits to resolve.

Any help would be appreciated:

Chris

Re: Error with expression

tresB — Fri, 16 Oct 2015 08:24:39 GMT

With a sample qvw, the case could have been investigated with a little more ease. Could you share one?

Re: Error with expression

Fri, 16 Oct 2015 08:32:21 GMT

Afraid I can't as it will have senstive information in. Otherwise I would.

Chris

Re: Error with expression

tresB — Fri, 16 Oct 2015 08:36:43 GMT

See if this helps: Preparing examples for Upload - Reduction and Data Scrambling

Re: Error with expression

Fri, 16 Oct 2015 08:51:32 GMT

Thanks. I have scrambled some of the fields.

Can I then remove the app from this post after its answered?

Re: Error with expression

tresB — Fri, 16 Oct 2015 09:23:45 GMT

Yes.

Re: Error with expression

karolina_ — Fri, 16 Oct 2015 16:15:50 GMT

You have 2 Neighbourhoods under the same address in your sample data - is that correct?
Button does selection on current selection ($) so result is different when you make selection for "Moss Vale".

Re: Error with expression

Fri, 16 Oct 2015 16:38:36 GMT

Hi Karolina,

Yes we have the same street in 2 of our areas. Falcon Drive is in Mountksip and then there is another Falcon Drive in Moss Vale.

Chris

Re: Error with expression

karolina_ — Fri, 16 Oct 2015 17:27:28 GMT

Sounds like you need AND on two diffrent fields.

Since you compare simple [NPV Total] maybe u can add flag in load script and count flags and use them in set analysis in app?

if([NPV Total]<=-5000,-1,if([NPV Total]>=5000,1,0)) AS Flag

Button would then select -1 in field Flag

or add variables vRed, vAmber, vGreen - in the button set vRed=1 and others as 0 and multiple your expressions:
=Count( {<[NPV Total]={"<-5000"}>} [Address])*vRed

=Count({<[NPV Total]={">=-4999<=4999"}>} [Address])*vAmber

=Count( {<[NPV Total]={">=5000"}>} [Address])*vGreen

Re: Error with expression

Tue, 20 Oct 2015 11:14:47 GMT

Hi Karolina,

Right.That make sense but not sure how to do this. How do we get the vRed, vAmber etc to work? Just copying and pasting your expression comes up with an error in the script saying vRed is a bad field name. Do we need to add something to the script?

Sorry if this is really silly question..still learning!

Chris

Re: Error with expression

karolina_ — Tue, 20 Oct 2015 11:34:28 GMT

Flag would be much better & quicker & nicer, but with variables it can be like in attached app.

Re: Error with expression

rubenmarin — Tue, 20 Oct 2015 11:41:19 GMT

Hi Chris, I used this expression in 'RED NPV' button action:

= '(' & '"' & concat({$ <Address={"=Avg([NPV Total])<-4999"}>} Address, '"' & '|' & '"') & '"' & ')'

Re: Error with expression

Tue, 20 Oct 2015 11:52:38 GMT

Thnak very much Karolina. Have both options now to use.

Much appreciated.

Chris

Re: Error with expression

karolina_ — Tue, 20 Oct 2015 11:58:26 GMT

Would that work on different set of data?

Actually it's giving now 135 reds in stead of 136

Re: Error with expression

rubenmarin — Tue, 20 Oct 2015 12:49:02 GMT

True, that's because the address '3 Ciztxx Zfbkm' is in Moss Vale and Mountskip, so filtering only by address will lead to this kind of unexpected results, filters should apply to a key that combines neigbourhood and address to avoid this.

Re: Error with expression

rubenmarin — Tue, 20 Oct 2015 12:55:34 GMT

Maybe changing the action field from Address to UPRN and use this expression:

=	'(' & '"' & concat({$ <UPRN={"=Avg([NPV Total])<-4999"}>} UPRN, '"' & '\|' & '"') & '"' & ')'