topic NOT MATCH in set analysis in QlikView

NOT MATCH in set analysis

Fri, 14 Jan 2011 21:36:21 GMT

Hi,

I am replacing chart expression

Count(DISTINCT if(Hours>0 AND Job <>1 AND NOT match(PersonID,1,2,20,21,22,23,24,25,26,27,28,29,998,999),Person_Name))

by set analysis.... how to add NOT MATCH condition in set analysis expression. Ia m trying something like this

=count({$<PaidDate=P(Date), Hours={'>0'}, Job={'<>1'}, PersonID{'<>{1,2,20,21,22,23,24,25,26,27,28,29,998,999}'} >} DISTINCT Person_Name)

And also like this

=count({$<PaidDate=P(Date), Hours={'>0'}, Job={'<>1'},PersonID={'=NOT MATCH(PersonID,1,2,20,21,22,23,24,25,26,27,28,29,998,999)'} >} DISTINCT Person_Name)

Thanks in Advance

NOT MATCH in set analysis

Fri, 14 Jan 2011 21:41:01 GMT

Use the exclusion operator: FIELD -= {VALUES}

So:

..., PersonID-={1,2,20,21,22,23,24,25,26,27,28,29,998,999}>} ...

Person ID does not equal any of those values.

NOT MATCH in set analysis

Fri, 14 Jan 2011 21:43:50 GMT

try this

=count({$<PaidDate=P(Date), Hours={'>0'}, Job-={'1'}, PersonID-={1,2,20,21,22,23,24,25,26,27,28,29,998,999} >} DISTINCT Person_Name)

to exclude son values con the set analysis you have to use PersonID - = {-={1,2,20,21,22,23,24,25,26,27,28,29,998,999}

Regards

NOT MATCH in set analysis

Fri, 14 Jan 2011 21:47:06 GMT

Nope its not working... and also what about remaining conditions in my expression.... are they correct?

NOT MATCH in set analysis

Fri, 14 Jan 2011 21:58:44 GMT

What results are you getting? All nulls? All nulls suggests a syntax error:

Here's a full expression:

Count({<Hours={'>0'}, Job-={1},
 PersonID-={'1,2,20,21,22,23,24,25,26,27,28,29,998,999'}>} DISTINCT Person_Name)

NOT MATCH in set analysis

Fri, 14 Jan 2011 22:02:52 GMT

Not Nulls... in correct data.... I am testing it with comparing old expr's data ....

NOT MATCH in set analysis

Fri, 14 Jan 2011 22:08:08 GMT

I should get something like 81, 91..... and the total is 157.... But its 157 in all rows...

NOT MATCH in set analysis

Fri, 14 Jan 2011 22:14:12 GMT

Can you provide a sample? When you're getting nulls, it's usually a syntax error that can be fixed. If you're getting incorrect data, then it's hard to troubleshoot without seeing the data. Do you have null values in any of the fields using Set Analysis?

The 157 in all rows sounds odd, since you don't have a total in there. What is the exact expression you are using?

I would try to look at the data on a more detailed level. Look at specific examples and see which ones are counted by the new expression that weren't counted by the first. Putting Person_Name temporarily into your table as a dimension should help with that.

NOT MATCH in set analysis

Fri, 14 Jan 2011 22:25:44 GMT

I have attached sample QV file..

NOT MATCH in set analysis

johnw — Sat, 15 Jan 2011 01:12:53 GMT

It's not 157 in all rows on my machine (v9SR6). I didn't carefully check every row, but both charts appear to have the same results.

NOT MATCH in set analysis

Wed, 19 Jan 2011 03:44:12 GMT

even i am using the same version... but i see 157 in all rows...