topic Re: Filter all fields in table based on input variables in QlikView

Filter all fields in table based on input variables

jagannalla — Tue, 19 Jul 2011 14:27:24 GMT

Hi,

Before going to my content can you please see my qvw file...

In my qvw file i had 3 fields A,B,C. I want to filter each field base on input variables.

For Eg:

To filter A, i'm taking vMinA=3 & vMaxA=11 variables in input box.

Now, in table i want to see A field with values between this range 3 to 10 only...

Like this i want to apply for all other fields also at a time.

Re: Filter all fields in table based on input variables

swuehl — Tue, 19 Jul 2011 16:00:45 GMT

Mr Nokia,

you can do this in a straight table with a hidden expression like in my example.

Hope this helps,

Stefan

Filter all fields in table based on input variables

jagannalla — Wed, 20 Jul 2011 05:07:35 GMT

Hi,

If one of the variable are empty it is not display any data in straight table. And also i'm not getting exact result in table.

Re: Filter all fields in table based on input variables

kaushiknsolanki — Wed, 20 Jul 2011 05:55:51 GMT

HI,

Have a look at the attched file.

Hope this is what you want.

Regards,

Kaushik Solanki

Re: Filter all fields in table based on input variables

Wed, 20 Jul 2011 06:18:31 GMT

Hi Nokia,

You can see the attached example.

Best regards.

Filter all fields in table based on input variables

jagannalla — Wed, 20 Jul 2011 06:31:16 GMT

kaushik/iuliancc,

Sorry i'm not able to open your file. B'coz i'm using personal edition.

Can you please say the steps

Re: Filter all fields in table based on input variables

kaushiknsolanki — Wed, 20 Jul 2011 06:41:11 GMT

HI,

Its not possible to filter data in the table box, the reason is table box is used to just display the raw data.

So you can use the seperate listbox to filter the data.

For filtering field A, try below.

Right click on the sheet -> New Sheet Object -> List box -> In field Drop down select Expression.

and type below expression.

=if(A >= $(vMinA) and A<=$(vMaxA),A)

Try same for the rest of the fields.

Regards,

Kaushik Solanki

Re: Filter all fields in table based on input variables

Wed, 20 Jul 2011 06:56:17 GMT

Hi,

Put this script in your "DataSet.qvw" example and reload data

TAB1:

LOAD * INLINE [

A, B, C

1, 66, 5

2, 33, 100

4, 77, 56

10, 29, 90

3, 49, 10

20, 35, 20

11, 99, 40

13, 31, 50

];

TAB2:

LOAD

num(A) as Aa

RESIDENT TAB1

WHERE A > '$(vMinA)' and A < '$(vMaxA)'

;

CONCATENATE

LOAD

num(B) as Bb

RESIDENT TAB1

WHERE B > '$(vMinB)' and B < '$(vMaxB)'

;

CONCATENATE

LOAD

num(C) as Cc

RESIDENT TAB1

WHERE C > '$(vMinC)' and C < '$(vMaxC)'

;

Maybe the result is what you want.

Regards.

Filter all fields in table based on input variables

jagannalla — Wed, 20 Jul 2011 07:04:16 GMT

But i want to filter all fields in single object.

I tried like this i had taken straight table then i add a dimension as ID, and expressions as

1.sum({<A={">=$(vMinA)<=$(vMaxA)"}>} A)

2.sum({<B={">=$(vMinB)<=$(vMaxB)"}>} B)

3.sum({<C={">=$(vMinC)<=$(vMaxC)"}>} C)

- Now it is working good for me. But i'm creating qvd for this straight table in button action with macro.

- When i didn't load B then i'm getting 0's in B expression. But i don't want to see this column in created qvd file when it is not loaded.

load

Rowno() as ID,

// B,

from table.qvd;

Re: Filter all fields in table based on input variables

kaushiknsolanki — Wed, 20 Jul 2011 07:07:57 GMT

Hi,

In presentation tab of the straight table peoperty, you can hide a column when there is some condition.

Try to put condition there to hide that column.

Regards,

Kaushik Solanki

Filter all fields in table based on input variables

jagannalla — Wed, 20 Jul 2011 07:12:20 GMT

iuliancc,

In your code if i don't load B then i'll face the problem. B'coz i'm loading fields with help of one variable(vFields=A,B,C) i mean like this:

Load

$(vFields)

from table1.qvd;

Filter all fields in table based on input variables

jagannalla — Wed, 20 Jul 2011 07:16:25 GMT

Kaushik,

What condition i need to put there Can you plz help me some more.

Re: Filter all fields in table based on input variables

kaushiknsolanki — Wed, 20 Jul 2011 07:19:22 GMT

HI,

Try this.

if(isnull(B),0,1)

Meaning if B is null then dont show else show.

Regards,

Kaushik Solanki

Filter all fields in table based on input variables

jagannalla — Wed, 20 Jul 2011 07:22:54 GMT

Thanks a lot kaushik it's working...I'll approach you once again if i required anything more.

Filter all fields in table based on input variables

jagannalla — Wed, 20 Jul 2011 07:26:41 GMT

But when i load B also it is hiding kaushik...after keeping condition in presentaiton

Re: Filter all fields in table based on input variables

kaushiknsolanki — Wed, 20 Jul 2011 07:36:27 GMT

Hi,

Try this.

if(sum(B) = 0,0,1)

Regards,

Kaushik Solanki

Filter all fields in table based on input variables

jagannalla — Wed, 20 Jul 2011 07:40:53 GMT

Thanks a lot kaushik it's working...I'll approach you once again if i required anything more.

Re: Filter all fields in table based on input variables

kaushiknsolanki — Wed, 20 Jul 2011 07:43:03 GMT

Hi,

Please mark this post as answered.

And incase you have any problem create a new post.

Regards,

Kaushik Solanki

Filter all fields in table based on input variables

Sat, 10 Mar 2012 21:56:08 GMT

Hi Kaushik,

hope u remeber me...u helped me before in solving a problem.........thsi time too i 'm looking towards u

Hi,

I've an expression like: sum(distinct Sales)/1000 in a st. table having 8

dimensions...now the expr can range from 0 to any big value.....i want to create an

input box where the user can enter a number & the expression shud show only the

values wich are greater than the value entered by the user.

Plz help

Re: Filter all fields in table based on input variables

kaushiknsolanki — Tue, 13 Mar 2012 04:23:15 GMT

Hi,

Offcourse I remember.

Anyways I would request you to create a new discussion on this topic so that it is not mixed under the current one, and this will help others to solve their issue if they have the same problem.

Also give me example.

Regards,

Kaushik Solanki