Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

check to see if a value equals selected filters

I have loaded a table into my script with the columns 'Condition' and 'Target'. How can I check to see if my 'Condition' value equals filters selected?

For example:

Filters are SubRegion, Quarter, and GBU. So if I have 'Brazil' selected for SubRegion, 'Q2' selected for Quarter, and 'ABS' selected for GBU, how do I match it to my Condition value that is 'BrazilQ2ABS'?

I have tried the following:

if(Condition = concat(getfieldselection(SubRegion),getfieldselection(Quarter),getfieldselection(GBU)),Target,0)

but it doesn't appear to be matching. Help please!

11 Replies
jpe
Not applicable

Re: check to see if a value equals selected filters

Is this for a chart or text box. 

I think one of these will work, but if it doesn't, provide the context (Chart, text box, variable etc...)

if(  Condition = only( Subregion) & only(Quarter) & only(GBU),  Target , 0)

if(  Condition = only( Subregion) & only(Quarter) & only(GBU),  Only( Target) , 0)

if(  Condition = only( Subregion) & only(Quarter) & only(GBU),  Sum( Target) , 0)

Not applicable

Re: check to see if a value equals selected filters

it is for an expression in a bar chart. I originally had nested ifs but there are 133 possible conditions so nested ifs don't work. I will try these, thanks

Not applicable

Re: check to see if a value equals selected filters

Jonathan, none of those worked

jpe
Not applicable

Re: check to see if a value equals selected filters

If you put this in a text box, what is the resulting value displayed  ? You need to select 1 value for each of the 3 dimensions first.

=only( Subregion) & only(Quarter) & only(GBU)

Also, is Target numeric or textual data?

Not applicable

Re: check to see if a value equals selected filters

Jonathan,

when I put that expression in a text box it appears to pull up the correct data but it is not matching to the Condition value. do I need to specify a data type somewhere? Condition is a text value and Target is a numeric value

For example Condition value is EMCIEQ2ABS

and I get EMCIEQ2ABS returned with your above statement but it's not matching (or not changing the associated target).

jpe
Not applicable

Re: check to see if a value equals selected filters

Is the condition / target in a data island ?  There should be a way to create a concatenated key between the tables in your data model and the condition table... that would be the best solution.  

Can you post your data model ?

Not applicable

Re: check to see if a value equals selected filters

Untitled.pngIt is on a data island. I was wondering about that. I created a key by importing a like column (SubRegion). Here is a screenshot of my data model.

When I do =only(SubRegion)&only(Quarter)&only(GBU) it gives me what I want but when I do =if(Condition = only(SubRegion)&only(Quarter)&only(GBU),Target,0) I get 0 with the same filters applied. it's not matching the results to Condition

jpe
Not applicable

Re: check to see if a value equals selected filters

What is in the 'Key' field ? 

I would join the quarterfilters and alm table together .

then i would make a new key that concatenates the 3 fields (subregion,quarter,GBU)  together and alias it also as condition.

At the bottom of your script you would have something like

---------------

left join (ALM)

load *

resident QuarterFilters;

drop table QuarterFilters;

noconcatenate

ALM1:

Load

          *,

          Subregion&Quarter&GBU as Condition

resident ALM;

drop table ALM;

-------------------

Then the main table that has both ALM and the quarterfilters will be joined on Condition and your chart expression should just be Target or sum(Target) .

Not applicable

Re: check to see if a value equals selected filters

I can't combine the tables because the QuarterFilters table is a breakout and remodeling of the ALM table.

I was able to create a trigger that worked perfectly using your =only(SubRegion)&only(Quarters)&only(GBU) as the search string and Condition as the Field with a Select in Field Action. It was working great for me yesterday and today it has suddenly decided to stop working. Any idea why that happened and how I can get it to work again?