topic Re: How to filter data based on expression value? in QlikView

How to filter data based on expression value?

Wed, 28 May 2014 00:01:18 GMT

Hi,

Here is my straight table structure.

Client	Bal Sheet	Rev	ROA
ABC	1222	20	16
CDE	9899	3000	303

Basically I have Client as the dimension and Bal Sheet, Rev and ROA as expressions

Interesting part is ROA expression is calculated using Bal Sheet and Rev

ROA = (Rev / bal Sheet ) * 1000

Now I wanted to have a filter to show ROA < 50 , ROA between 50 to 100 , ROA > 100

I tried to do a data island with list box filter. On selecting the ROA filter list box (let say I choose < 50) I see ROA column displays values < 50 but it also contains other rows which are not < 50 (displayed as nulls).

I basically only want to display those rows matching the ROA filter.

Hope I am clear.

Please let me know how to achieve this ?

Thanks

Re: How to filter data based on expression value?

SergeyMak — Wed, 28 May 2014 01:45:40 GMT

You can move this logic to the load script

something like

[tmpgrp]:

LOAD ClientId, Month

IF(SUM(Rev)/SUM([Bal Sheet])*1000<50,'<50',

IF(SUM(Rev)/SUM([Bal Sheet])*1000>=100, '>100', '>=50<100') AS Filter

Resident your_data_table

GROUP BY ClientId, Month;

LEFT JOIN (your_data_table)

LOAD *

Resident tmpgrp;

DROP Table tmpgrp;

Re: How to filter data based on expression value?

jagan — Wed, 28 May 2014 01:52:54 GMT

Hi,

Can you attach some sample file?

Regards,

Jagan.

Re: How to filter data based on expression value?

Anonymous — Wed, 28 May 2014 03:51:11 GMT

Attach a Sample file

Re: How to filter data based on expression value?

Wed, 28 May 2014 13:33:03 GMT

Sergey, ROA has to be calculated on the QV front end.

I currently have the Load script resolution but the issue is we have multiple dimensions.

So when slicing the data - it shows wrong results

So its better to do from the front end side

Re: How to filter data based on expression value?

Wed, 28 May 2014 13:35:23 GMT

hello Jagan and Nitin,

I don't know how to provide a sample file. Our QVW is huge with so many dimensions/slicers, big data model.

But basically I wanted to filter the expressions.

Thanks

Re: How to filter data based on expression value?

sasikanth — Wed, 28 May 2014 14:08:37 GMT

Hi Dimple

Your BalSheet and Rev are also caluculated Exp ? if then provide the calculation part

if not then

Use in listbox expression

Let var=(rev/Balsheet)*1000

if($(var)<50, 'ROA<50',

if($(var)>50 and $(var)<100, 'ROA 50 to 100',

if( $(var)>100,'ROA>100')))

Hope this will help you

Re: How to filter data based on expression value?

Wed, 28 May 2014 15:32:06 GMT

Hi Sasikanth

I think you are trying to move the same logic from load script to list box. The problem is there are many dimension fields and if we statically calculate ROA - then its a problem

What I did is introduce new columns based on ROA value

< 50 , > 100 , 50-100 columsn

The problem is it displays the values correctly in the buckets.

how can I hide the null rows now ?

Re: How to filter data based on expression value?

maxgro — Wed, 28 May 2014 19:27:20 GMT

maybe (see attachement)

if(Rev / [Bal Sheet] * 1000 >100, dual('>100',100),

if(Rev / [Bal Sheet] * 1000 >50, dual('50...100',50),

dual('0...50',0)

)))

Re: How to filter data based on expression value?

Thu, 29 May 2014 01:54:36 GMT

This looks promising. Can you please explain how this works?

Basically I am not able to understand how the ROA field is getting affected when you change the list box?

Thanks

Re: How to filter data based on expression value?

Thu, 29 May 2014 12:37:02 GMT

I am new to Qlikview. Can some one please explain how Massimo Grossi's solution works ?

Thanks

Re: How to filter data based on expression value?

Thu, 29 May 2014 12:45:51 GMT

Hi dimple.

If(Rev / [Bal Sheet] * 1000 >100, dual('>100',100) <- this expression checks if rev / bal * 1000 is greater than 100. Dual returns two values: a text value (in this case '>100' and a number value (in this case 100). So selecting >100 in the list box filters to values that satisfy Rev / Bal Sheet * 1000 > 100 ( in this case CCC and CDE).

The rest of the function works in a similar way.

Re: How to filter data based on expression value?

Anonymous — Thu, 29 May 2014 13:02:34 GMT

//this technique of stacking load statements is called preceeding loads. Search for HIC + Preceeding loads in the community

LOAD *,

Class(ROA ,50 ,'ROA ' ) as ROA //replace the 50 with any increment you want. Class allows you to split any figure into classes. Very useful. Could be combined with aggregate functions.

;

LOAD *,

( (Rev / bal Sheet ) * 1000 ) as ROA ; //Calculate the item price. in the preceeding load above, you can reuse ItemPrice and QlikView will understand its content. Very powerful load technique

LOAD * INLINE [

Client, bal Sheet, Rev,

ABC, 1222, 20,

CDE, 9899, 3000,

]

;

Hope this helps...