Using the Excel Function - COUNTIF
Hi Folks,
I'm hoping that someone can show me how to use the excel function of COUNTIF within Lavastorm v6
Attached files
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
-
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
Please sign in to leave a comment.
Comments
3 comments