topic Re: Set Expression Question in App Development

Set Expression Question

Anonymous — Wed, 29 Nov 2017 04:05:59 GMT

I'm still learning set expressions along with aggr, and find myself easily stumped by problems that I think should be easily solved. Having spent the better part of a day on this one & getting nowhere, I thought it best to ask for help!

My purely fictitious test dataset is as follows:-

Shelves:

load * inline [

InventoryMonth,ShelfNumber,Fruit

2,1,Apple

2,1,Orange

2,1,Pear

2,2,Banana

3,1,Apple

3,1,Orange

3,2,Banana

3,3,Apple

3,2,Persimmon

3,2,Pomegranate

3,2,Passionfruit

4,1,Pear

];

The chart needs to display the count of shelves that had more than X types of fruit on them during the month.

Let's say X >= 2. For month=2, only ShelfNumber 1 met that criteria, so the value on the chart should be 1. For month=3, ShelfNumber 1 and 2 both had 2 or more types of fruit, so the chart would show 2. Finally for month=4, the value would be zero.

I've tried numerous approaches to this, none of which seem to be close to working. I expected a simple solution such as the following to work...

count(distinct {<ShelfNumber = {'=count(distinct Fruit) >= 2'}>} ShelfNumber)

... however it returns values of 2, 2, 1 instead of the expected 1, 2, 0

Any assistance would be much appreciated!

Re: Set Expression Question

sunny_talwar — Wed, 29 Nov 2017 04:12:40 GMT

You are not just checking the Count(DISTINCT Fruit) against ShelfNumber, but ShelfNumber and InventoryMonth, right? In that case, create a new field in the script

Shelves:

LOAD AutoNumber(InventoryMonth&ShelfNumber) as Key,

load * inline [

InventoryMonth,ShelfNumber,Fruit

2,1,Apple

2,1,Orange

2,1,Pear

2,2,Banana

3,1,Apple

3,1,Orange

3,2,Banana

3,3,Apple

3,2,Persimmon

3,2,Pomegranate

3,2,Passionfruit

4,1,Pear

];

and then try this

Count(DISTINCT {<Key = {"=Count(DISTINCT Fruit) >= 2"}>} Key)

Re: Set Expression Question

sunny_talwar — Wed, 29 Nov 2017 04:18:17 GMT

Alternatively, you can use aggr() function with both ShelfNumber and InventoryNumber to do this

=Count(DISTINCT Aggr(If(Count(DISTINCT Fruit) >= 2, ShelfNumber), ShelfNumber, InventoryMonth))

Re: Set Expression Question

Anonymous — Wed, 29 Nov 2017 04:19:16 GMT

Brilliant, thankyou!

Re: Set Expression Question

Anonymous — Wed, 29 Nov 2017 04:20:21 GMT

Yes, that would work as well. I was trying to avoid the use of an IF statement though, as the actual data I'm working with has a huge number of records.

Re: Set Expression Question

sunny_talwar — Wed, 29 Nov 2017 04:21:17 GMT

Yup, I would go with set analysis any day over the use of Aggr() and if()