In UniQuery, how do you get the count of unique values found while doing a BREAK.ON

Question

I know I can get the counts for how many individual entries are in each unique groups of records with the following.

LIST CUSTOMER BREAK-ON CITY TOTAL EVAL "1" COL.HDG "Customer Count" TOTAL CUR_BALANCE BY CITY

And I end up with something like this.

Cust...... City...... Customer Count Currently Owes

         6 Arvada     1                        4.54
           ********** -------------- --------------
           Arvada     1                        4.54

       190 Boulder    1                        0.00
         1 Boulder    1                       13.65
           ********** -------------- --------------
           Boulder    2                       13.65
 ...
                      ============== ==============
TOTAL                 29                      85.28
29 records listed

Which becomes this, after we suppress the details and focus on the groups themselves.

City...... Customer Count Currently Owes

Arvada     1                        4.54
Boulder    2                       13.65
Chicago    3                        4.50
Denver     6                        0.00
...
           ============== ==============
TOTAL      29                      85.28
29 records listed

But can I get a count of how many unique grouping are in the same report? Something like this.

City...... Customer Count Currently Owes City Count

Arvada     1                        4.54          1
Boulder    2                       13.65          1
Chicago    3                        4.50          1
Denver     6                        0.00          1
...
           ============== ============== ==========
TOTAL      29                      85.28         17
29 records listed

Essentially, I want the unique value count integrated into the other report so that I don't have to create an extra report just for something so simple.

SELECT CUSTOMER SAVING UNIQUE CITY

17 records selected to list 0.

score 1 · Answer 1 · answered Feb 12 '20 at 23:00

I swear that this should be easier. I see various @ variables in the documentation that hint at the possibility of doing this easily but I have never been about to get one of them to work.

If your data is structured in such a way that your id is what you would be grouping by and the data you want is stored in Value delimited field and you don't want to include or exclude anything you can use something like the following.

In UniVerse using the CUSTOMER table in the demo HS.SALES account installed on many systems, you can do this. The CUSTID is the the record @ID and Attribute 13 is where there PRICE is stored in a Value delimited array.

    LIST CUSTOMER BREAK-ON CUSTID TOTAL EVAL "DCOUNT(@RECORD<13>,@VM)" TOTAL PRICE AS P.PRICE BY CUSTID DET.SUP

Which outputs this.

               DCOUNT(@RECORD<13>,@
Customer ID    VM).................    P.PRICE

          1    1                        $4,200
          2    3                       $19,500
          3    1                        $4,250
          4    1                       $16,500
          5    2                        $3,800
          6    0                            $0
          7    2                        $5,480
          8    2                       $12,900
          9    0                            $0
         10    3                       $10,390
         11    0                            $0
         12    0                            $0
               ====================    =======
               15                      $77,020

That is a little juice for a lot of squeeze, but I hope you find it useful.

Good Luck!

I don't think I explained it properly. I'll use an example from the demo so that you can see what I mean. — Script Wolf, Feb 13 '20 at 15:20
Yeah, I don't think that is an easy thing to do with this tool. There really isn't a hook that i have found to get a new column associated with those break ON things. I think RETRIEVE just outputs the records as it reads them and then when the break hits between records it fires something that does the totaling. It doesn't carry how many time it has hit the break. — Van Amburg, Feb 13 '20 at 20:13

score 0 · Answer 2 · answered Jun 17 '20 at 23:31

0

Since the system variable @NB is set only on the total lines, this will allow your counter to calculate the number of TOTAL lines, which occur per unique city, excluding the grand total.

LIST CUSTOMER BREAK-ON CITY TOTAL EVAL "IF @NB < 127 THEN 1 ELSE 0" COL.HDG "Customer Count" TOTAL CUR_BALANCE BY CITY

I don't have a system to try this on, but this is my understanding of the variable.

answered Jun 17 '20 at 23:31

Dennis Bartlett

1

It sounds promising but when I run this I get the following error for the @NB, "Virtual Attribute Error: In Virtual field qz1zq. Illegal '@' character." – Script Wolf Jun 19 '20 at 16:54

score 0 · Answer 3 · answered Jun 07 '23 at 01:45

0

I'm not offering a solution but want to point out the RetrieVe keyword for a Count is ENUM. TOTAL EVAL "1" is an unnecessary kludge at least in U2.

LIST CUSTOMER BREAK.ON CITY ENUM @ID

answered Jun 07 '23 at 01:45

Galaxiom

109
4

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/34508639) – James Jun 13 '23 at 23:15

In UniQuery, how do you get the count of unique values found while doing a BREAK.ON

3 Answers3