topic Using exclude E() with multiple set modifiers in QlikView

Using exclude E() with multiple set modifiers

Thu, 09 Feb 2017 01:57:15 GMT

I am trying to return some data by excluding particular items.

Example: Return all the sales that are not dogs or were not paid by cash

Method 1

The following function does not give me what I want, because there are nulls in the data that I also want to return

Sum( <${

<Animal -= {'Dog'}> +

}> Sales)

Method 2

I was hoping to use exclude instead, as it handles the nulls. However, this won't work either - I think it is because E() cannot handle multiple set modifiers.

Sum( <${

OrderNumber = E({$ <Animal = {'Dog'}> +

<Payment = {'Cash'}>})

}> Sales)

Any thoughts or help would be appreciated.

Re: Using exclude E() with multiple set modifiers

sunny_talwar — Thu, 09 Feb 2017 02:08:48 GMT

May be this:

Sum(<${OrderNumber = E({$<Animal = {'Dog'}>}) + E({$<Payment = {'Cash'}>})>} Sales)

or this

Sum(<${OrderNumber = E({$<Animal = {'Dog'}>}) * E({$<Payment = {'Cash'}>})>} Sales)

Re: Using exclude E() with multiple set modifiers

sunny_talwar — Thu, 09 Feb 2017 02:15:22 GMT

Or this:

Sum(<${OrderNumber = {"=Animal <> 'Dog' or Payment <> 'Cash'}>} Sales)

Re: Using exclude E() with multiple set modifiers

Thu, 09 Feb 2017 06:04:02 GMT

Fantastic! Thanks, Sunny.

I appreciate your prompt and accurate response