topic Re: Excluding data that's a combination of two fields in QlikView

Excluding data that's a combination of two fields

saurabhk18 — Mon, 14 May 2018 15:58:40 GMT

Hi,

The screenshot you see is a representation of three fields (listboxes). There is a many-to-many relationship between each pair of fields.

We have the foll. requirement: The user would like a new filter to exclude data associated with PSL = PSL1 and Region= (Northern US, Southern US) on the dashboard. The problem I see here is the many-to-many relationship angle I mentioned above. For example, if the user selects Category = Cat3 and the exclusion filter, she would like to see data for Cat3 that is NOT associated with PSL = PSL1 and Region= (Northern US, Southern US). But Cat3 itself might be related to PSL=PSL1.

Is this requirement feasible?

Thanks,

Saurabh

Re: Excluding data that's a combination of two fields

felipedl — Mon, 14 May 2018 16:45:29 GMT

HI Saurabh,

If I understood correctly, you want to create filters that exclude some combinations from your data right?

If so, you could do the following:

ExampleData:

Load * Inline

[

PSL,Cat,Region,Value

PSL1,Cat1,Northern US,10

PSL1,Cat2,APAC,12

PSL1,Cat3,Northern US,3

PSL1,Cat4,Southern US,66

PSL1,Cat5,APAC,123

PSL2,Cat2,Southern US,87

PSL2,Cat4,Northern US,103

PSL2,Cat5,APAC,55

PSL2,Cat7,Southern US,78

PSL3,Cat1,Southern US,123

PSL3,Cat3,Northern US,64

PSL3,Cat5,MENA,33

PSL3,Cat7,LatAm,34

PSL4,Cat1,Northern US,66

PSL4,Cat2,LatAm,54

];

Create a variable with the following content: {<PSL-={'PSL1'},Region-={'Northern US','Southern US'}>}

And use it in a filter like this:

=aggr(only($(vSetExclude)Cat),Cat)

Whick will create a filter for the Cat field, where PSL <> PSL1 and Region <> {'Northern US','Southern US'}.

As you can see by the image, the new filter boxes only show values that are associated with the above rule.

Felipe.

Re: Excluding data that's a combination of two fields

saurabhk18 — Mon, 14 May 2018 18:13:54 GMT

Hi Felip,

Thanks for the detailed response. Yes, I basically want to exclude certain combinations of data. However, if one of the data lines is as follows:

PSL2, Cat1, Northern US, 20

Then the New Region list box in your image will incorrectly exclude Northern US.

Is it possible to create a single field that combines the PSL and Region fields to excludes certain data lines? For example, if I want to only see data that contains PSL = PSL1 and Region=Northern US, I can create a list box with the following expression:

PSL='PSL1' and Region='Northern US'

But I'm unable to create a field to exclude data with this combination.

Thanks,

Saurabh

Re: Excluding data that's a combination of two fields

felipedl — Mon, 14 May 2018 18:50:56 GMT

Tottaly not getting what you need for filters than.

First you said it needed to exclude fields that had the combination PSL <> PSL1 and (Region <> Northern US and Region <> Southern US).

If you only need to exclude PSL = PSL1 and Region = Northern US, only modification would be {<PSL-={'PSL1'},Region-={'Northern US'}>} to the variable containing the set analysis exclusing clause.

The exclusion, according to your parameters are functional, as seen by the image:

The table has all the values, and the filters the exception rules, so the new filters only have the values that don't present the PSL1 and Northern US.

Why shouldn't it exclude the line you mentioned?

Attached the sample app i created.

Re: Excluding data that's a combination of two fields

saurabhk18 — Mon, 14 May 2018 20:23:09 GMT

Thanks for the QVW, Felip. I'll explain using an example from your data set.

PSL1,Cat4,Southern US,66 //data line 1

PSL2,Cat4,Northern US,103 //data line 2

The variable has been defined as {<PSL-={'PSL1'},Region-={'Northern US'}>}

New Cat does not have Cat 4 despite the presence of data line 1.

I'm actually looking for a single expression that I can use in a list box. If I filter data using this list box, I should be able to see data excluding the combinations of PSL1 and (Northern US, Southern US). I tried several expressions, but it seems that they don't work when used in list boxes. For example, I used this:

if(([PSL]='PSL1' and not(Match([Region],'Northern US','Southern US'))) OR

[PSL]<>'PSL1' and match([Region],'Northern US','Southern US','APAC','LatAm','MENA') ,'Yes')

i.e. the data should have either PSL1 and regions that are not Northern or Southern US, or non-PSL1 and all regions.

If I select Yes in the list box created using the expression above, the Current Selections box shows Region=ALL and PSL=ALL, which is not what I want.

Thanks,

Saurabh

Re: Excluding data that's a combination of two fields

felipedl — Mon, 14 May 2018 20:39:29 GMT

Owwwwwww,

I see what I did wrong,

The correct exclusion set is this one:

{<Cat=P({<Cat-={'Cat1'}>}Cat),Region=P({<Region-={'Northern US'}>}Region)>}

In this case, its getting all possible values for Cat and Region the things you need

See if this is what you need.

Felipe.

Re: Excluding data that's a combination of two fields

saurabhk18 — Wed, 16 May 2018 18:55:02 GMT

Thanks, Felip. But I want to set up a single filter for the users. If this filter is selected, data related to PSL=PSL1 and Region={Northern US, Southern US} together should be removed. I have chosen to add this logic to the QV code (flag rows which have this combination) instead of using chart functions. I think that might be easier.

Thanks again!

-Saurabh