topic Re: Ideas on adding complex filter in QlikView

Ideas on adding complex filter

Mon, 18 May 2015 04:56:09 GMT

Hi, I have a question on where and how a filter logic should be applied to a dashboard I am developing.

The filter logic in the original report that I am replacing is a complex filter that goes something like:

1 AND 2 AND ((3 AND 4) OR (5 AND 6)) AND 7

where each number is a filter (e.g. 1 = OpportunityRecordType='Renewal' or 'Standard' or 'Partner')

I'm stuck if I should implement this in the load, as a button on the UI that applies these, as a trigger event, or what. It's been bugging me for a week now, advice would be greatly appreciated!

Re: Ideas on adding complex filter

marcus_sommer — Mon, 18 May 2015 15:23:13 GMT

I would try to apply this within the script in a field which the user could simply select within the UI. A part like your 1 could be solve with a match() like:

match(OpportunityRecordType, 'Renewal', 'Standard', 'Partner')

- Marcus

Re: Ideas on adding complex filter

Mon, 18 May 2015 16:50:27 GMT

Thanks for the suggestion. I considered it, but a lot of the Boolean logic takes fields from different tables. I think that'd create a Frankenstein table, wouldn't it?

Re: Ideas on adding complex filter

maxgro — Mon, 18 May 2015 19:02:25 GMT

yes but if the logic of the filter is complex, you have to choose between complex in script or complex in the user interface; usually, in similar situations I choose the script; if you want and can, post the logic, fields and tables

Re: Ideas on adding complex filter

Mon, 18 May 2015 19:59:18 GMT

Boolean expression is as follows:

1 AND 2 AND ((3 AND 4) OR (5 AND 6)) AND 7

1. OpportunityRecordType = 'Renewal' or 'Standard' or 'Partner Deal'

2. SalesStage = '01 - UC' or '02 - VO' or '03 - QO' or '04A - DS' or '04B - PS' or '05 - NC' or '06- WDE' or '6 - WDE' or 'Lost'

3. OpportunityType = 'Renewal'

4. SalesMotion = 'New Solution'

5. Opportunity Type != 'Renewal'

6. SalesMotion = 'Care Pack' or 'Day 1' or 'Per Event' or 'New Solution' or 'Renewal' or 'Conversion' or 'Bus Type 'W''

7. PipelineExclusion != 'Y'

all of that, PLUS:

CampaignName={*NEWLead* | *NewLead* | *Employee Lead Gen* | TM;* | LOGO*}

I cannot post the entire table structure, as it's quite large, but here is a snippet with some of the corresponding tables and the fields corresponding to the above logic highlighted.

Re: Ideas on adding complex filter

maxgro — Mon, 18 May 2015 21:47:24 GMT

- flag1 on Opportunity table: 1 and 2 and 7

- flag2 on OpportunitItem: (3 or 4) and (5 or 6)

to create this flag you need a join to add OpportunityType in OppurtunityItem (or applymap)

I suppose the relation OpportunityItem : Opportunity is N : 1

- flag3 on Campaign

----------

flag1

match(OpportunityRecordType, 'Renewal', 'Standard', 'Partner Deal')

and match(SalesStage, '01 - UC', '02 - VO', '03 - QO', '04A - DS', '04B - PS', '05 - NC', '06- WDE', '6 - WDE', 'Lost')

and not match (PipelineExclusion, 'Y')

flag2

(OpportunityTypeBis = 'Renewal' or SalesMotion = 'New Solution')

and

(OpportunityTypeBis <> 'Renewal' or match(SalesMotion, 'Care Pack', 'Day 1', 'Per Event', 'New Solution', 'Renewal', 'Conversion', 'Bus Type W'

flag3

wildmatch(CampaignName, '*NEWLead*', '*NewLead*', '*Employee Lead Gen*', 'TM;*', 'LOGO*')

----------

in the user interface you can AND the 3 flags

another solution could be to only add one (or two) flag but I can't find the right table, maybe OpportunityItem?

Re: Ideas on adding complex filter

Thu, 21 May 2015 21:49:12 GMT

Great idea. I've implemented the fields, but am having difficulty AND'ing them in the UI. I created a button and added the 3 filters as Actions, but it acts as if it is OR'ed. i.e. - It filters on Action 1 and Action 2, but if it finds no hits for Action 3, it just shows records for Action 1 and 2.

The expected behavior of an AND dictates that it should thus show no records (or at least that's the behavior I'm looking for). Any advice?