Skip to main content

Dataset Alert Merging Multiple Alerts

Scenario

The previous document [《Smart Use of Alert Push Card Details to Individuals》](../0-Selected Articles/4-Smart Use of Alert Push Card Details to Individuals.md) introduced how to use dataset alerts to push data details to corresponding users. However, since each row of data that meets the conditions will be pushed as a separate message, when there are too many data rows, frequent pushing can cause the following problems:

  • The same alert content email or message is sent N times, giving you a headache?

  • The same topic content is packed full, making your eyes dizzy?

  • What to do if you don't want to receive alerts on weekends and holidays?

图片.png

This article will introduce how to merge alerts into one when the same recipient has multiple rows of data that meet the conditions, and only push on working days. The final effect is shown in the figure below.

图片.png

Implementation Principle

Use ETL to aggregate data so that each user has only one row of data, displayed with line breaks; determine whether the date is a working day.

Implementation Steps

1. Concatenate Multiple Fields into One Field

In ETL, first aggregate the data by date, user, and store name, then create a new calculated field to concatenate multiple fields that need to be displayed in the alert into one field.

concat('Store Name：',[Store Name],', Target Count：',[Target Count])
or 
'Store Name：'||[Store Name]||', Target Count：'||[Target Count]

图片.png

2. Data Alignment

From the preview results above, each row has different lengths and cannot be aligned like a table. Here we need to insert full-width spaces after "Store Name" to align the data.

  1. Add a "Add Calculated Column" operator, use window function to calculate the maximum length of [Store Name], subtract to get the [Length Difference] with the current store name:**
max(length([Store Name]))over(partition by [User],[Date])-length([Store Name])
  1. Adjust the order of the 2 "Add Calculated Column" operators, modify the [Content1] field formula to:**
concat('Store Name：',[Store Name],repeat(' ',[Length Difference]),', Target Count：',[Target Count])

图片.png

3. Merge Multiple Rows into One Row with Line Breaks

Create a new "Group Aggregation", aggregate by date and user, create a new calculated field [Non-compliant Items] and drag it to the value column.

concat_ws('\n',collect_set([Content1]))

Note: Line break characters do not take effect in ETL preview and dataset preview, but they are effective when displayed in the frontend (cards, push notifications), so please ignore the preview results at this time.

图片.png

4. Determine Holidays

*Create calculated fields [Working Day Judgment] and [Today], filter current day data and output the dataset. If you only need to push alerts from Monday to Friday, you can use the function DAYOFWEEK (Sunday is 1, Monday is 2) to filter; if you need to accurately determine working days and all holidays, you need to prepare a special holiday date table and associate it, please refer to [《Holiday Data Distinction and Comparison》](7-Holiday Data Distinction and Comparison.md) for details.

case when DAYOFWEEK(current_date())>=2 and DAYOFWEEK(current_date())<=6 then 1 
else 0 end

图片.png

5. Set Dataset Alert

图片.png

Note: DingTalk and WeChat push messages have character length limits (maximum 512 characters, non-Chinese character count). If the limit is exceeded, they cannot be displayed completely. In this case, it is recommended to use email push (different versions of email push display may have differences, to be fixed) or only display summaries in text and view card details through links.