Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

...

For a version of this function computed over a rolling window of rows, see ROLLINGMODE Function.

D s
snippetBasic

MODEIF
d-codelang-syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextpivot value:
modeif(count_visits, health_status == 'sick') group:postal_code limit:1

...

modeif(count_visits, health_status == 'sick')

Output: Returns the mode of the values in the count_visits column as long as health_status is set to sick

...

.

...

D s
snippetSyntax

MODEIF
d-codelang-syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextpivot value:
modeif(function_col_ref, test_expression) [group:group_col_ref] [limit:limit_count]

modeif(function_col_ref, test_expression) [group:group_col_ref] [limit:limit_count]


ArgumentRequired?Data TypeDescription
function_col_refYstringName of column to which to apply the function
test_expressionYstring

Expression that is evaluated. Must resolve to true or false

...

DateRegionOrderCount
1/6/2017r0178
1/6/2017r0297
1/13/2017r0192
1/13/2017r0290
1/20/2017r0197
1/20/2017r0284

TransformTransformation: 

To assist, you can first calculate the week number for each row:

d-

...

trans
RawWrangletrue
p03Value'weekNumber'
Typestep
WrangleTextderive type: single value:

...

weeknum(Date) as: 'weekNumber'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueweeknum(Date)
p03NameNew column name
SearchTermNew formula

Then, you can use the following aggregation to determine the most common order value for each region during the second half of the year:

d-

...

trans
RawWrangletrue
p03Value50
Typestep
WrangleTextpivot group: Region value:

...

modeif(OrderCount, weekNumber > 26) limit: 50
p01NameRow labels
p01ValueRegion
p02NameValues
p02Valuemodeif(OrderCount, weekNumber > 26)
p03NameMax number of columns to create
SearchTermPivot columns

Results:

Regionmodeif_OrderCount
r0185
r02100

...