topic Re: Simple question? Optionally apply a filter in QlikView

Simple question? Optionally apply a filter

datanibbler — Wed, 30 Nov 2016 08:13:38 GMT

Hi,

I have a LOAD in the script I'm currently working on with two filters - but in one specific situation I have just implemented the code for, this LOAD is also needed - but with only one of the two filters, the second one would lead to 0 records being found ...

I have a variable that is 1 when this specific situation is there, so I could built two LOADs in a big IF_THEN clause - but is there a slimmer way, to write the LOAD only once and optionally apply that filter?

Can I define that filter as a variable, for example, and just make it "1=1" if the filtering function is not needed?

Thanks a lot!

Best regards,

DataNibbler

Re: Simple question? Optionally apply a filter

rubenmarin — Wed, 30 Nov 2016 08:21:03 GMT

Hi DataNibbler, maybe you can use the variable to fill another variable with the filter, something like:

LET vFilter = If($(vApplyFilter)=0, null(), 'where conditions');

And use it in the query:

LOAD fields

Resident/from/select

$(vFilter);

Re: Simple question? Optionally apply a filter

datanibbler — Wed, 30 Nov 2016 09:08:19 GMT

Hi Ruben,

I'll try ...

Re: Simple question? Optionally apply a filter

datanibbler — Wed, 30 Nov 2016 11:31:13 GMT

Ah - no, I just realized I could not do it that way even if it worked (up to now it doesn't, but that's only logical in this instance) - I have filtered the base_table from which I do this LOAD (as a RESIDENT LOAD) on a certain field, which rules out its use in this specific situation. Complicated to explain, but the effect is easy - the specific ID I'm searching for in this specific situation is no longer in that version of the base_table, even if I were to use a NULL-filter - so I have to use a different base table.

Best regards,

DataNibbler

Re: Simple question? Optionally apply a filter

marcus_sommer — Wed, 30 Nov 2016 11:41:11 GMT

Hi DataNibbler,

it's not quite clear for me what do you want to do - but you are speaking about loading one field and searching for a certain fieldvalue - for this it might be useful or an alternative to use a load-loop over fieldvalue() or searching the value per fieldindex().

- Marcus

Re: Simple question? Optionally apply a filter

datanibbler — Fri, 02 Dec 2016 07:01:53 GMT

Hi Marcus,

I just tricked myself in searching for a performance_improvement:

- At the very outset of my script, I split one big table I need several times into two parts, depending on the value in one

specific field: Field is TRANCODE, values are SPRECEIVE and all others.

- At that other point in the script which lead to this post I had a LOAD from one of these parts and I had two filters in place - one for that very field which I don't really need to check anymore ...

- I saw that in a very specific situation I could use that same LOAD, but I would have to deactivate just that filter (I would have needed that field TRANCODE unfiltered) - so I could not have done this because the LOAD was from that table that was already filtered on that field.

So, in the end, I did use an IF_THEN_ELSE clause because I had to use a LOAD from the other part of that table in that specific situation, in which case all the field_names are different.

Thus, this issue is solved, kind of ...

Best regards,

DataNibbler