LAE

Hi Folks,

I'm hoping that someone can show me how to use the excel function of COUNTIF within Lavastorm v6

Attached files

example.txt

What I am looking for is the below result, as I want to keep the original listing and no grouped values:

Fruit     Colour   Item_Number   Count

Apple    Red         101                    5
Apple    Green      102                    2
Apple    Yellow      103                    1
Apple     Red         101                    5
Apple     Red         101                    5
Apple     Green      102                    2
Apple     Red         101                    5
Apple     Red         101                    5

The AggEx mode provides functionality equivalent to the Excel CountIF.

The groupCount() operator provides the count of the items that match the configured criterion. The groupCount() operator can accept a filter predicate e.g. groupCount(Item_Number == 101). However, as with the Excel CountIF function, this would only generate the count for the number of items that matched the specified  predicate. Additional aggregate expressions would be required for each distinct predicatethat needed to be counted.

Instead of using this approach it is simpler to just use the groupCount() operator without a specified filter predicate and then define the GroupBy property to identify the field whose values will be used as the predicates.

Which results in the following output data:

A Lookup node can be used to join the output of the AggEx node with the original data to generate the required results.

The groupCount() operator can also be used in a Filter node to generate a running total for a specified predicate.

Attached files

Count_Items_in_Group.brg

As an aid to users migrating to the Data360 Analyze product, a similar approach would be used in Data360 Analyze but using the Aggregate node instead of the AggEx node.

Attached files

Count_Items_In_Group - 26 Aug 2021.lna

Hi Adrian,

Thanks so much for that, works perfectly :)

Also for putting an explanation