calculate no.of records

Posted by vignesh kumar on 02-Mar-2016 07:06

Hi,

Can you help me in calculating the no. of records in query for two different tables and then need to export it in excel ?

Posted by Ruben Dröge on 04-Mar-2016 03:01

DEFINE TEMP-TABLE ttSubTotals
    FIELD poltype   AS CHARACTER
    FIELD subtotal  AS DECIMAL
    FIELD recuwm    AS INTEGER
    FIELD recclm    AS INTEGER
Index ttix IS UNIQUE PRIMARY poltype.
    
DEF VAR subtotal AS DECIMAL.
DEF VAR subrecuwm AS INTEGER.
DEF VAR subrecclm AS INTEGER.
DEF STREAM data-export.

OUTPUT STREAM data-export TO VALUE("c:\temp\output.csv").
PUT STREAM data-export UNFORMATTED "Class, Amount, No. of records in uwm100, No.of records in clm100" SKIP.

FOR EACH uwm100 WHERE uwm100.comdat > 12/01/14 AND uwm100.comdat < 12/01/2015 NO-LOCK BREAK BY poltype:
    FOR EACH clm100 WHERE clm100.policy = uwm100.policy AND clm100.rencnt = uwm100.rencnt AND clm100.endcnt = uwm100.endcnt NO-LOCK:
        ACCUMULATE clm100.policy (COUNT).
    END.
    ACCUMULATE uwm100.amount (COUNT SUM BY uwm100.poltype).
    subtotal    = (ACCUM SUM BY uwm100.poltype uwm100.amount).
    subrecuwm   = (ACCUM COUNT BY uwm100.poltype uwm100.amount).
    subrecclm   = subrecclm + (ACCUM COUNT clm100.policy).
    IF LAST-OF(poltype) THEN DO:
        CREATE ttSubTotals.
        ASSIGN
            ttSubTotals.poltype     = uwm100.poltype
            ttSubTotals.subtotal    = subtotal
            ttSubTotals.recuwm      = subrecuwm
            ttSubTotals.recclm      = subrecclm 
            .
        subrecclm = 0.  
        EXPORT STREAM data-export DELIMITER "," ttSubtotals.
    END.
END.

All Replies

Posted by Alon Blich on 02-Mar-2016 07:41

not exactly sure what you want to do but...

if the table is millions or even billions of records long you can use the slib/utils/fast-rec-cnt.p in the standard libraries project at the oehive.org

the utility uses proutil idxblockreport to calc the records. note that this is not an exact number but it's pretty close.

i timed it at roughly 1 second per 500,000,000 records.

<code>

define var i as int no-undo.

run slib/utils/fast-rec-cnt.p( "<physical dbname>", "table", output i).

message i.

</code>

if it's a small number of records and you're already running over them and exporting them to excel then there's no problem counting them.

to create an excel file you can either use a plain csv file or you might want to look at the free docxfactory project at docxfactory.com

hth

Posted by George Potemkin on 02-Mar-2016 08:07

> i timed it at roughly 1 second per 500,000,000 records.

If I recall correctly Excel can't open a file with more than a million rows. FOR EACH query can stop counting the records after the specified limit.

Posted by Alon Blich on 02-Mar-2016 08:32

you're right. you couldn't export it if it was more than roughly a million.

btw i wrote the fast-rec-cnt.p after reading your post about proutil idxblockreport and howto use it for fast record counts a few years ago :) thank you! :)

Posted by dbeavon on 02-Mar-2016 09:24

You may have more options if you are willing to use SQL92 instead of ABL.  The SQL technology in general (and OE-SQL92 in particular) is great for most reporting-related purposes.  

For example, if you have an index on the table than then the "SELECT count(*)" should be resolved without reading the base table data.  In contrast, I don't think normal ABL has any mechanism at all for reading only index data without getting down into the base table's data.  

Posted by Brian K. Maher on 02-Mar-2016 09:43

I am assuming Windows platform since Excel doesn’t run on others...
 
Search the knowledgebase for either “excel” or “telerik spreadprocessing” or “infragistics excel”.  You will find samples.
 
As for the query, you need to provide much more details.

Posted by Alon Blich on 02-Mar-2016 11:35

> I am assuming Windows platform since Excel doesn’t run on others...

you may want or need to generate the file on the server running unix/linux and there's openoffice/libreoffice and others...

Posted by vignesh kumar on 02-Mar-2016 22:50

Hi All,

Thanks for your responses.

Am running in the windows only and each table has around 600 records only.

Posted by vignesh kumar on 02-Mar-2016 23:13

My query looks like this,

output to "C:\Vignesh_NTT\Dfsa.csv".

export delimiter "," "Policy no" "Claim" "Renewal cnt" "End cnt" "Gross prem" "class" "comdat".

for each uwm100 where comdat >= 12/29/14 and comdat <= 12/28/15 and releas = yes

                     no-lock by cntry:

For each clm100 where clm100.policy = uwm100.policy and

                      clm100.rencnt = uwm100.rencnt and

                      clm100.endcnt = uwm100.endcnt no-lock:

Now i want to calculate the no.of records in uwm100 and clm100 table and need to export it.  

Posted by Ruben Dröge on 03-Mar-2016 03:03

Something like this?

DEF VAR i AS INTEGER.
DEF VAR ii AS INTEGER.
DEF STREAM data-export.

OUTPUT STREAM data-export TO VALUE("c:\temp\output.csv").
PUT STREAM data-export UNFORMATTED "Policy no, Claim, Renewal cnt, End cnt, Gross prem, class, comdat" SKIP.
FOR EACH uwm100 WHERE uwm100.comdat > 12/01/14 AND uwm100.comdat < 12/01/2015 NO-LOCK BY cntry:
    ACCUMULATE uwm100.policy (COUNT).
    i = (ACCUM COUNT uwm100.policy).
    EXPORT STREAM data-export DELIMITER "," uwm100.
    
    FOR EACH clm100 WHERE clm100.policy = uwm100.policy AND clm100.rencnt = uwm100.rencnt AND clm100.endcnt = uwm100.endcnt NO-LOCK:
        ACCUMULATE clm100.policy (COUNT).
        EXPORT STREAM data-export DELIMITER "," clm100.
    END.
    ii = ii + (ACCUM COUNT clm100.policy).
END.
OUTPUT STREAM data-export CLOSE.

MESSAGE i SKIP(1)
        ii VIEW-AS ALERT-BOX.

Posted by vignesh kumar on 03-Mar-2016 05:55

Thank you its very helpful.

In the same query if i need to calculate for different types,suppose a table has different policy types i need to find the sum of amounts of that type and also the no. of records in that type,

something like this,

for each uwm100 where trndat >= 12/29/14 and trndat <= 12/28/15 and releas = yes

                     no-lock break by poltyp:

if first-of(poltyp) then do:

        assign

         a = 0

   i = 0

   .

    end.

    if poltyp = "FIA" then

    assign

       a = a + uwm100.prem_t

       i = i + 1.

    else if poltyp = "FFF" then

    assign

       a = a + uwm100.prem_t

       i = i + 1.

    else if poltyp = "ECA" then

    assign

       a = a + uwm100.prem_t

        i = i + 1.

    else if poltyp = "EMB" then

    assign

       a = a + uwm100.prem_t

        i = i + 1.

    else if poltyp = "CSV" then

    assign

       a = a + uwm100.prem_t

        i = i + 1.

I need to do the same for two tables and export the variable a & i.

can you please help on this.

Posted by Ruben Dröge on 03-Mar-2016 07:23

DEFINE TEMP-TABLE ttSubTotals
    FIELD poltype   AS CHARACTER
    FIELD subtotal  AS DECIMAL
Index ttix IS UNIQUE PRIMARY poltype.
    
DEF VAR subtotal AS DECIMAL.

FOR EACH uwm100 WHERE uwm100.comdat > 12/01/14 AND uwm100.comdat < 12/01/2015 NO-LOCK BREAK BY poltype:
    FOR EACH clm100 WHERE clm100.policy = uwm100.policy AND clm100.rencnt = uwm100.rencnt AND clm100.endcnt = uwm100.endcnt NO-LOCK:
        ACCUMULATE clm100.amount (COUNT SUM).
    END.
    ACCUMULATE uwm100.amount (COUNT SUM BY uwm100.poltype).
    subtotal = (ACCUM SUM BY uwm100.poltype uwm100.amount).
    IF LAST-OF(poltype) THEN DO:
        CREATE ttSubTotals.
        ASSIGN
            ttSubTotals.poltype  = uwm100.poltype
            ttSubTotals.subtotal = subtotal
            .
        DISPLAY ttSubTotals.    
    END.
END.

MESSAGE "Records in uwm100 = " + STRING((ACCUM COUNT uwm100.amount)) SKIP(1)
        "Records in clm100 = " + STRING((ACCUM COUNT clm100.amount)) SKIP(1)
        VIEW-AS ALERT-BOX.

Posted by pliscki on 03-Mar-2016 07:45

DEFINE VARIABLE iTotalRec         AS INTEGER NO-UNDO.
DEFINE VARIABLE iTotalRecFiltered AS INTEGER NO-UNDO.

FOR EACH uwm100 NO-LOCK WHERE
         uwm100.trndat >= 12/29/14 AND
         uwm100.trndat <= 12/28/15 AND
         uwm100.releas  = yes
   BREAK BY uwm100.poltyp:

   ACCUMULATE uwm100.prem_t (COUNT BY uwm100.poltyp).

   /* it will only get the accumulated count from records that match the criteria */
   IF LAST-OF(uwm100.poltyp) AND 
      LOOKUP(uwm100.poltyp, 'FIA,FFF,ECA,EMB,CSV') > 0 THEN
      iTotalRecFiltered = iTotalRecFiltered + (ACCUM COUNT BY uwm100.poltyp uwm100.prem_t).
END.
/* gets the total number of records from the whole table */
iTotalRec = (ACCUM COUNT uwm100.prem_t).

Posted by vignesh kumar on 04-Mar-2016 01:49

Thank you...! The query looks good and able to get my results.

But one concern is in clm100 table you are accumulating by amount but i dont need amount wise in that table i just need to export the no.of records based on clm100.class as below last column.

And also  in uwm100 table i also need to export the no.of records by poltype whereas we are exporting only the total no.of records in table.

The poltype and class are same in two fields.

Please export the fields dont need to display it.

My excel should be like this,

Class            Amount (uwm100)                No.of record in uwm100              No.of records in clm100

FIA                  11033.12                           100                                              50

FFF                 52080.6                              200                                              50

ECA                 326.26                                100                                             100

EMB                 4166.45                              50                                               100

CSV                1423.33                                100                                            100

Posted by Ruben Dröge on 04-Mar-2016 03:01

DEFINE TEMP-TABLE ttSubTotals
    FIELD poltype   AS CHARACTER
    FIELD subtotal  AS DECIMAL
    FIELD recuwm    AS INTEGER
    FIELD recclm    AS INTEGER
Index ttix IS UNIQUE PRIMARY poltype.
    
DEF VAR subtotal AS DECIMAL.
DEF VAR subrecuwm AS INTEGER.
DEF VAR subrecclm AS INTEGER.
DEF STREAM data-export.

OUTPUT STREAM data-export TO VALUE("c:\temp\output.csv").
PUT STREAM data-export UNFORMATTED "Class, Amount, No. of records in uwm100, No.of records in clm100" SKIP.

FOR EACH uwm100 WHERE uwm100.comdat > 12/01/14 AND uwm100.comdat < 12/01/2015 NO-LOCK BREAK BY poltype:
    FOR EACH clm100 WHERE clm100.policy = uwm100.policy AND clm100.rencnt = uwm100.rencnt AND clm100.endcnt = uwm100.endcnt NO-LOCK:
        ACCUMULATE clm100.policy (COUNT).
    END.
    ACCUMULATE uwm100.amount (COUNT SUM BY uwm100.poltype).
    subtotal    = (ACCUM SUM BY uwm100.poltype uwm100.amount).
    subrecuwm   = (ACCUM COUNT BY uwm100.poltype uwm100.amount).
    subrecclm   = subrecclm + (ACCUM COUNT clm100.policy).
    IF LAST-OF(poltype) THEN DO:
        CREATE ttSubTotals.
        ASSIGN
            ttSubTotals.poltype     = uwm100.poltype
            ttSubTotals.subtotal    = subtotal
            ttSubTotals.recuwm      = subrecuwm
            ttSubTotals.recclm      = subrecclm 
            .
        subrecclm = 0.  
        EXPORT STREAM data-export DELIMITER "," ttSubtotals.
    END.
END.

Posted by vignesh kumar on 04-Mar-2016 03:19

Many Thanks!! [:)]

It fetches exactly what i need.

And also thanks for your quick responses.

This thread is closed