topic Re: Create filter from calculated field in QlikView

Create filter from calculated field

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

this is the situation, i've a table like this

id	in	out	result
a	14	11	3
b	17	23	-6
c	86	56	30
d	43	32	11

this is the load script:

LOAD id,

in,

out

FROM ....

and result = in - out

Now i'd like a filter where i can group the result value, for example:

result < 0

result > 0 and result < 10

result > 10 and result < 20

result > 20 and resul < 30

so i can filter only the values that i want.

Can i create a filter on the calculated field "result"?

Re: Create filter from calculated field

gerry_hdm — Thu, 05 Apr 2018 11:16:50 GMT

it's going with Intervallmatch()

Greetings gerry

Re: Create filter from calculated field

chinnuchinni — Thu, 05 Apr 2018 11:20:42 GMT

try this:

if ( in - out< 0,'result < 0',if(in - out> 0 and in - out< 10, 'result > 0 and result < 10', if(in - out> 10 and in - out< 20,'result > 10 and result < 20',if(in - out> 20 and in - out< 30,'result > 20 and result < 30')))) as Result status

Re: Create filter from calculated field

sunny_talwar — Thu, 05 Apr 2018 11:21:19 GMT

Look here

Buckets

Re: Create filter from calculated field

Anonymous — Thu, 05 Apr 2018 11:22:04 GMT

I can't use Intervallmatch() because i create the result field with Straight Table and not on load

Re: Create filter from calculated field

MarcoWedel — Thu, 05 Apr 2018 21:06:27 GMT

maybe something like

Class(YourStraightTableResultExpression, 10)

hope this helps

regards

Marco

Re: Create filter from calculated field

Nagaraju_KCS — Fri, 06 Apr 2018 04:29:05 GMT

Try this...

Load *, IF(result<0, 'lesthan0',

IF(result>0 and result<=10, '0-10',

IF(result>10 and result<=20, '11-20',

IF(result>20 and result<=30, '21-30')))) as ResultBucket;

LOAD * INLINE [

id, in, out, result

a, 14, 11, 3

b, 17, 23, -6

c, 86, 56, 30

d, 43, 32, 11

];

Re: Create filter from calculated field

Anonymous — Fri, 06 Apr 2018 05:55:54 GMT

try this:

LOAD*,

in-out as result;

LOAD * INLINE [

id, in, out

a, 5, 3

b, 22, 4

c, 6, 7

d, 8, 4

e, 19, 6

f, 7, 8

];

take a list box an write a below expression in the field <exp>

=if(result<0,'result<0',if((result > 0 and result < 10),'result>0 and result<10',if((result>10 and result<20) ,'result > 10 and result < 20','result>20')))

Re: Create filter from calculated field

sasiparupudi1 — Fri, 06 Apr 2018 13:14:13 GMT

May be like this?

Re: Create filter from calculated field

effinty2112 — Fri, 06 Apr 2018 13:35:57 GMT

Hi Andrea,

Try this as an expression in a listbox:

=Aggr('<=' & 10*Ceil((in-out)/10),id)

Cheers

Andrew

Re: Create filter from calculated field

Mark_Little — Fri, 06 Apr 2018 14:11:34 GMT

Hi,

I would first in script use the Peek function to calculate your Result column.

Something like

LOAD Date,

IN,

Out,

IF(ROWNO()=1,

IN - Out,

Peek(Result,-1)+ (IN - Out)

) AS Result

FROM YourTable,

Then you could either do a proceeding load and use an if statement to calculate your buckets or front end use the Class() Function to so the result in the buckets.

A Deminsion some like

CLASS(Result,10,'Bucket')

Mark