CountIf in BeastMode or ETL ?

Options
user12446
user12446 Member
in Magic ETL

Good Morning Community

I have a 5M+ row dataset where I need to develop a "COUNTIF" function based on a 'Category" column (image enclosed/table below). Any help would be appreciate

Data contains too many distinct categories to list individually in beast-mode by name - kindly assist. 

 

 

 

DatePriceCategoryReportCOUNTIF GOAL
2019-Feb10,000.00Type 89Report 325
2019-Feb10,000.00Type 89Report 1075
2019-Feb10,000.00Type 89Report 175
2019-Feb10,000.00Type 89Report 535
2019-Feb10,000.00Type 89Report 195

 

 

 

Comments

  • Unknown
    Unknown
    Options

    Just to clarify,  Are you looking to check the Unique no. of reports under each category against the goal of 5?

  • user12446
    user12446 Member
    Options

    No, the goal is not dependent on unique report names - only the number of times the category (i.e. Type 89) occurs in the column. 

  • Unknown
    Unknown
    Options

    Try this beast mode:

    COUNT(DISTINCT category) 

     

    This will give you a count of unique categories.

  • user12446
    user12446 Member
    Options

    tried this, the result shows row data only not the column data. i.e. Goal = 1 not 5 

  • Randyb
    Randyb Member
    Options

    Its most likely doing that because of the report component which is different it appears on each row.

    If you sum the count(Distinct) it should give you the value of 5 that your looking for.

    If I'm interpreting this correctly.

    Randy

Categories