Using the aggregate( function inside the arrayfilter( function

Panorama X
1

Is it possible to use the aggregate( function as the formula in the arrayfilter( function? This code:

local lvtally,lvtemp
lvtally=""
lvtemp=arraybuild(cr(),"",{«StateProvince»})
lvtemp=arraydeduplicate(lvtemp,cr())

lvtally = arrayfilter(lvtemp,cr(),|||aggregate({StateProvince},"count")|||)

produces 1500 (the total number of records in the file) as every result. Changing the final statement to:

 lvtally = arrayfilter(lvtemp,cr(),|||aggregate({import()},"count")|||)

gives the same result.

2

I believe that’s what it’s supposed to do. According to the documentation the formula is ignored when you use the count option. What you want to do is use the query option.

lvtally = arrayfilter(lvtemp,cr(),|||aggregate({},"count",{import()=StateProvince})|||)

If the count method is used, the formula parameter is ignored, and can be left empty. (This is more useful when combined with the queryformula parameter described below.)

aggregate({},"count") ☞ returns the number of visible records in the database

By adding a query formula, you can restrict the aggregate to a subset of the database. This example calculates the total amount of all records that are more than 30 days old.

aggregate({Amount},"+",{Date<today()-30}) ☞ total of all records that are more than 30 days old
aggregate({Salary},"average",{Department="Engineering"} ☞ average salary of engineers
aggregate({},"count",{Salary > 2 * aggregate("Salary","average")})
☞ number of staff members that make more than twice the average salary
3

I had already tried that without success -

22 pm
22 pm727×85 14 KB

4

I have been working on this problem with Michael before he posted the question. I think the problem is that the import( function is not being evaluated when placed inside the formula of the aggregate( function which is inside the array filter function.

5

Dave, did you get your example to work?

6

No. I hadn’t even tried. I didn’t have a suitable database for testing. Your original question only mentioned one formula, and it was behaving exactly the way the documentation said it would, so I thought it was simply a case of misreading the documentation.

7

Michael,
Do you recall whether you ever got import() function to work when nested inside the query of an aggregate function, further nested within the formula of an arrayfilter()? I’m getting the same “No value for import( function” runtime error from

hasSBWroff=arraystrip(arrayfilter(hasSBWroff," ",|||?(aggregate({«Amount$»},"sum",{«Chart Number»=import() and «Procedure Code»="SMBALWROFF"},"WS Transactions",true())<0,import(),"")|||)," ")

Searching the forum I’m reminded I’d reported the same error message during text imports with rearrange in 2017. Which Jim listed as issue #761 in BitBucket and fixed by there giving import() an initial “” value. But from what’s written at #761 the scope of his fix was limited to database import. Perhaps something similar is happening here. I stumbled on this with 10.1.2; haven’t tried it in the Beta yet.