topic Re: Re: problem writing set expression (exclusion) in QlikView

problem writing set expression (exclusion)

Thu, 07 Aug 2014 09:14:17 GMT

The idea behind the expression is:

I want to count all the products where the weight is 0 and the adjusted_weight does NOT equal 0,1.

This is the expression that i had (doesn't give the wanted result):

count({<,emptyings_weight={0}, emptyings_adjusted_weight-={'0,1'}>}product_id)

Can anyone see what is wrong?

Kind regards,

Katleen

Re: problem writing set expression (exclusion)

sujeetsingh — Thu, 07 Aug 2014 09:15:22 GMT

Try this

count({$<emptyings_weight={0}, emptyings_adjusted_weight-={'0,1'}>}product_id)

Re: problem writing set expression (exclusion)

Gysbert_Wassenaar — Thu, 07 Aug 2014 09:16:06 GMT

Remove the first comma. count({<emptyings_weight={0}, emptyings_adjusted_weight-={'0,1'}>}product_id) should work if your field names are the correct case-sensitive names.

Re: problem writing set expression (exclusion)

kaushiknsolanki — Thu, 07 Aug 2014 09:16:41 GMT

Hi,

Remove the first comma in set analysis.

Regards,

Kaushik Solanki

Re: problem writing set expression (exclusion)

Thu, 07 Aug 2014 10:15:56 GMT

All the suggested solutions don't give me the desired result.

I need the weight to be 0 AND the adjusted weight to NOT be 0.1.

Otherwise it should not count.

Any other ideas?

Re: problem writing set expression (exclusion)

Gysbert_Wassenaar — Thu, 07 Aug 2014 10:17:52 GMT

Sure, post the offending document so we can have a look with all the context available.

Re: problem writing set expression (exclusion)

jerrysvensson — Thu, 07 Aug 2014 10:25:10 GMT

count({<product_id = E({<emptyings_adjusted_weight = {'0,1'}>} product_id), emptyings_weight={0}>} product_id)

Re: Re: problem writing set expression (exclusion)

Thu, 07 Aug 2014 11:10:55 GMT

In attachment an example qvw file

Re: problem writing set expression (exclusion)

jerrysvensson — Thu, 07 Aug 2014 11:35:02 GMT

Did you try my suggestion? It should work fine.

Re: Re: problem writing set expression (exclusion)

Thu, 07 Aug 2014 11:47:26 GMT

Your suggestion is used in the example file, doesn't work unfortunalty.

Perhaps you can take a look at the qvw document to see why it doesn't work?

Kind regards,

Katleen

Re: problem writing set expression (exclusion)

Gysbert_Wassenaar — Thu, 07 Aug 2014 12:05:34 GMT

There are several issues. First you count product_id. That is a key field, so it's not clear which table should be used for the count. Never use a key field in a count, unless it's a count distinct.

Next, 0 is not 0,0. So weight={0} doesn't match any records. Use '0,0' instead.

Finally I cannot match your count of 482 with the data in the document you posted. I get a result of 2140 using this expression: count({< weight={'0,0'}, adjusted_weight-={'0,0','1,0'}>} emptyings_id ).This is the same number I get by adding all the fields in a table box object and making the selections using listboxes. First select Oudenaarde, next select 0,0 and 1,0 in the field adjusted_weight and invert the selection in that field by choosing select excluded. Finally select 0,0 in the field weight. The table box now shows 2140 records.

Re: problem writing set expression (exclusion)

jerrysvensson — Thu, 07 Aug 2014 12:07:12 GMT

count({<product_id = E({<emptyings_adjusted_weight = {'0,1'}>} product_id), emptyings_weight={'0,0'}>} product_id)

Gives result :2181.

You need to change your data model. Can you give us the SQL code that matches the 482 result?

Re: Re: problem writing set expression (exclusion)

Fri, 08 Aug 2014 06:13:05 GMT

My fault, I didn't make it very clear.

In the example file I simulated the result with selections.

The result should be 4630.

I checked it in SQL, You can see it in the attached file.

Kind regards,

Katleen