Syntax

GROUPCOUNTDISTINCT(<any>)

Description

Counts the distinct values of a group.

This is an aggregate function.

Example

Given the following data:

Customer_	Name	Item
2535	Jon	Zamioculcas
2888	Mike	Spider Lily
2535	Jon	Datura
2535	Jon	Dahlia
5788	Jon	Snapdragon
2888	Mike	Marguerite
2535	Jon	Windflower
2888	Mike	Elephant's Ear
2535	Jon	Chinese Evergreen
5788	Jon	Begonia
5788	Jon	Starfish Plant
2535	Jon	Hare's Foot Fern
2535	Jon	Venus Flytrap
3545	Monique	Red Flame Ivy

First create a group using GROUPBY(#RawData!Name)

Name
Jon
Mike
Monique

Then use the GROUPCOUNTDISTINCT(#RawData!Customer_), and the results are a count of each time an individual value appears in that column in relation to the GROUPBY() column.

Name	Customer_COUNTDISTINCT
Jon	2
Mike	1
Monique	1

Known issue:

When working with the GROUPCOUNTDISTINCT function on a dataset with a very high number of records in a group, out of memory exceptions might occur.

Cause:

In order to determine the true distinct value, GROUPCOUNTDISTINCT shifts all mapped information to disk on one reducer. This can cause disk space and performance/memory issues on large datasets. This is a known limitation that Datameer X is working to resolve.

Solution:

In order to work around this issue, an intermediary sheet along with a combination of GROUPBY and GROUPCOUNT functions is suggested as described below.

Original:

Sheet 1: user_id, product

Sheet 2: GROUPBY(Sheet1.product), GROUPCOUNTDISTINCT(Sheet2.user_id)

Workaround:

Sheet 1: user_id, produt

Sheet 2: groupby(Sheet1.product), groupby(Sheet1.user_id)

Sheet 3: group by(Sheet2.product), groupcount

This work around gives the same desired output of the GROUPCOUNTDISTINCT function.

Browser not supported