topic Set Analysis problem with -= operator in QlikView

Set Analysis problem with -= operator

johnpaul — Mon, 05 Sep 2011 06:21:08 GMT

I am trying to count the number of open tickets that don't have a parent ticket.

A ticket has a ticket number and a field is used to indicate if that ticket number has a parent ticket. If so, then I would like to ignore it in my count.

Here is the code:

Note that the Intellisense is complaining of the -=

This returns 0 records, when it should return 2885, so I have also tried IsChild={"<>Y"} which also gives me 0 records.

If I use IsChild={Y} then I get the correct count of 8.

No matter what I do, I just can't seem to get this to work!

Set Analysis problem with -= operator

SunilChauhan — Mon, 05 Sep 2011 06:50:34 GMT

IsChild-={"Y"}

u can try this also

IsChild-={'Y')

or else

share the sample

hope this help u

Set Analysis problem with -= operator

Mon, 05 Sep 2011 06:55:36 GMT

Try using this:

IsChild = {'*'} - {'Y'}

This will return you all the values excluding where IsChild is 'Y'.

Re: Set Analysis problem with -= operator

jonathandienst — Mon, 05 Sep 2011 09:08:14 GMT

The IsChild-={'Y'} is correct (in spite of the intellisense complaint). It will return values that have a value other than 'Y' in IsChild, but it will NOT include records with IsChild = null.

I cannot see your original expression, as the image is blocked where I am at the moment, but using count as an example, this is what you need to do to include null values in IsChild:

Count(SomeField) - Count({<IsChild={'Y'}>} SomeField)

Hope that helps

Jonathan

Re: Set Analysis problem with -= operator

Mon, 05 Sep 2011 12:49:03 GMT

Two suggestions:

1) Try using forced exclusion using tilda ("~")

E.g. {$ <~IsChild={"Y"}>}

2) Try using element function E()

E.g. {$ <TicketID = E( {1 <IsChild={"Y"}>}) >}

Re: Set Analysis problem with -= operator

johnpaul — Mon, 05 Sep 2011 13:38:27 GMT

Thanks for all the help.

I had/have tried all options and it seems that Jonathan's comment is where my issue is.

I had already tried doing a count all records then subtracting the ones which I didn't want, but I thought that was the long way around.

It makes sense if the NULL values are not counted as not 'Y' - seems obvious now!

Oh, and the original code was:

=count({<IsChild-={Y}>} DISTINCT Ticket_Number)