topic Re: How to use more than one expression in a Table Box in QlikView

How to use more than one expression in a Table Box

Mon, 29 Apr 2013 14:02:45 GMT

Good morning all,

I have created a table box to display the specific columns i am interested in for a report.

I have added list boxes so I can select values to filter the table box with.

I have three types of content that i need to display. Each are fairly simple and straight-forward but I would like to have the box display the values for all three filters at once so I can export it as a single report.

I have three sets of values. the first is easy, Display all active customers
The second is display all Failed and Merged customers that do not have a merged report (value = Yes)
The third is to display all Failed and merged customers with balances over $1,000,000.00

Lets say that Active, Merged and Failed use column named "Status"

Item two uses column named "Merged report" and item three uses column named "Recip Balance"

These three are mustally exclusive in that I can't apply all three filters in a row or they will exclude some of the entries in the other two filters.

What I need is a way to say display all records that meet any of the three criteria listed above so the results show in the table box.

But I do not want filter two or three to limit the active banks listed in filter one.

Any help out there on this one?

Steve

Re: How to use more than one expression in a Table Box

mphekin12 — Mon, 29 Apr 2013 14:31:34 GMT

Steve,

Why not create a 4th column using an IF statment. Something like:

=if((Status='Active') OR ((Staus='Failed' OR Status='Merged) AND Value='Yes') OR((Status='Failed' OR Status='Merged') AND Balance > 1000000),1,0).

You can then put a filter on this column and selecting 1 should display the report you need.

You could also put this logic in the LOAD statement and create a new flag field.

I hope this helps!

Re: How to use more than one expression in a Table Box

Gysbert_Wassenaar — Mon, 29 Apr 2013 15:10:04 GMT

You can do this in a straight chart. Add the fields you want to display as dimensions and add one expression. Something like this: sum({<Status={'Active'}>+<Status={'Merged','Failed'},[Merged report]={'Yes'}>+<[Recip Balance]={>1000000}>} 1). You can hide the expression on the Presentation tab of the properties window.

Replace Status, [Merged report] and [Recip Balance] with the exact case-sensitive names of the fields.

Replace 'Active' with the correct value that indicates an active status. Same for merged and failed and 'Yes' for [Merged report].