Discussion Board for collaboration on QlikView Scripting.
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?
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!
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)
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
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?
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).
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 ?
It 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
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)
drop table QuarterFilters;
Subregion&Quarter&GBU as Condition
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) .
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?