topic Re: Epression changes in QlikView

Epression changes

yogitamehta10 — Wed, 19 Aug 2015 09:28:49 GMT

Hi,

I have this expression in front end for every chart , now i want to transfer this to backend scripting for better performance or i can use set analysis

any suggections ?/?

if(isnull(avg(if([Measure] = 'E03',[ Weight]))),0,avg(if([Measure] = 'E03',[Weight])))

if(isnull(avg(if([Measure] = 'E04',[Weight]))),0,avg(if([Measure] = 'E04',,[Weight])))

if(isnull(avg(if([Measure] = 'E05',[Weight]))),0,avg(if([Measure] = 'E05',,[Weight])))

if(isnull(avg(if([Measure] = 'E06',[ Weight]))),0,avg(if([Measure] = 'E06',,[Weight])))

if(isnull(avg(if([SM Measure] = 'EP07',[ Weight]))),0,avg(if([Measure] = 'EP07',,[Weight])))

if(isnull(avg(if([Measure] = 'EP08',[Weight]))),0,avg(if([ Measure] = 'EP08',,[Weight])))

any suggections ?/?

Re: Epression changes

gautik92 — Wed, 19 Aug 2015 09:42:41 GMT

In mapping Table

Mapping Mapping Name

Load

Measure,

If(if(isnull(avg(if([Measure] = 'E03',[ Weight]))),0,avg(if([Measure] = 'E03',[Weight])))

if(isnull(avg(if([Measure] = 'E04',[Weight]))),0,avg(if([Measure] = 'E04',,[Weight])))

if(isnull(avg(if([Measure] = 'E05',[Weight]))),0,avg(if([Measure] = 'E05',,[Weight])))

if(isnull(avg(if([Measure] = 'E06',[ Weight]))),0,avg(if([Measure] = 'E06',,[Weight])))

if(isnull(avg(if([SM Measure] = 'EP07',[ Weight]))),0,avg(if([Measure] = 'EP07',,[Weight])))

if(isnull(avg(if([Measure] = 'EP08',[Weight]))),0,avg(if([ Measure] = 'EP08',,[Weight])))) as alias name,

Re: Epression changes

jonathandienst — Wed, 19 Aug 2015 09:57:30 GMT

You can simplify this a lot - RangeSum treats nulls as zeroes:

RangeSum(

avg(if(Measure = 'E03', Weight)),

avg(if(Measure = 'E04', Weight)),

avg(if(Measure = 'E05', Weight)),

avg(if(Measure = 'E06', Weight)),

avg(if(Measure = 'EP07', Weight)),

avg(if(Measure = 'EP08', Weight))

) As newField,

You will need a Group By for any non-aggregated fields in the LOAD expression

FROM ....

Group By ....

Re: Epression changes

yogitamehta10 — Wed, 19 Aug 2015 10:59:59 GMT

will this work

load [Measure],

if(isnull(avg([Weight])),0,avg([ Weight])) as [RULE1AV_temp]

Resident [Weight ]

Group by [Measure];

Re: Epression changes

sasiparupudi1 — Wed, 19 Aug 2015 18:12:15 GMT

It should work fine

Re: Epression changes

qlikviewwizard — Thu, 20 Aug 2015 02:37:51 GMT

Did you try?

Re: Epression changes

jonathandienst — Thu, 20 Aug 2015 05:17:21 GMT

It should, but this will be more efficient as it calculates the averages once each rather than twice:

load [Measure],

Alt(avg([Weight])),0) as [RULE1AV_temp]

Resident [Weight ]

Group by [Measure];

Re: Epression changes

Kushal_Chawda — Thu, 20 Aug 2015 05:36:34 GMT

Try this on front end.

=RangeAvg(avg({<[Measure] = {'E03'}>}[ Weight]),0)+

RangeAvg(avg({<[Measure] = {'E04'}>}[ Weight]),0)+

RangeAvg(avg({<[Measure] = {'E05'}>}[ Weight]),0)+

RangeAvg(avg({<[Measure] = {'E06'}>}[ Weight]),0)+

RangeAvg(avg({<[Measure] = {'E07'}>}[ Weight]),0)+

RangeAvg(avg({<[Measure] = {'E08'}>}[ Weight]),0)

from back end you can do this as suggested by Jonathan

Re: Epression changes

yogitamehta10 — Thu, 20 Aug 2015 05:40:04 GMT

oad [Measure],

if(isnull(avg([Weight])),0,avg([ Weight])) as [RULE1AV_temp]

where match([Measure],'EPM02',

EPM03',

'EPM04',

EPM05',

EPM06',

Resident [Weight ]

Group by [Measure];

i used this but now i have issue i have another expression with same type but mesaure match are diifferenrt for that .

lets say measure ='EP90', 'Ep34' and we will say it [RULE2AV_temp]

i want that to include in same script

how should i do ?

Re: Epression changes

Kushal_Chawda — Thu, 20 Aug 2015 05:53:54 GMT

create the Flag in Script for RULE1AV & RULE2AV like below

load [Measure],

if(isnull(avg([Weight])),0,avg([ Weight])) as [RULE1AV_temp],

'RULE1AV' as Flag

where match([Measure],'EPM02',

'EPM03',

'EPM04',

'EPM05',

'EPM06')

Resident [Weight ]

Group by [Measure];

concatenate

load [Measure],

if(isnull(avg([Weight])),0,avg([ Weight])) as [RULE2AV_temp],

'RULE2AV' as Flag

where match([Measure],'EPM90',

'EPM34')

Resident [Weight ]

Group by [Measure];

Then in front end use the Flag in expression

avg({<Flag={'RULE1AV'}>}[ Weight]) +

avg({<Flag={'RULE2AV'}>}[ Weight])

modify your expression as per your need