At times, you may want to mask specific street addresses by converting them to 100 block locations. This can be accomplished in Mark43 analytics using a custom Table Calculation.
To demonstrate this, use the Reports Data Explorer - Offense/Incident explore and add the following dimensions:
- [Report - Offense/Incident] - [Offense Location] - Street Number
- [Report - Offense/Incident] - [Offense Location] - Street Name
Click the [Run] button to preview your results:
Our goal is to round these numbers down to the nearest hundred value. One method to accomplish this is to subtract the modulo (i.e. remainder) from the street address after dividing by 100. For example, if we divide 1949 by 100 we have a modulo of 49. Substracting 49 from the original street address gives us the hundred block of 1900.
The code provided below will do the following:
- Identify the modulo when the street number is divided by 100
- Substract the modulo from the street number
- Concatenate a string of “ BLK “
- Concatenate the street name
To create the custom table calculation:
- Expand the Custom Fields section of the Reports Data Explorer - Offense/Incident
- Click the [new] button
- Choose Table Calculation
- Replace the default name (Calculation 1) with Block-Level Location
- Copy and paste the following code:
# Used to combine the calculated block number, a
# whitespace, BLK, a whitespace, and the street name
concat(
# The street address is stored as a string
# We must convert that to a number to substract
# the modulo after dividing by 100
to_number(
${vw_report_offenses.offense_location_street_number})
# Subtract the modulo (calculated below) from the
# original street number above
-
# To get the 100 block, identify the modulo after
# dividing by 100
mod(
to_number(${vw_report_offenses.offense_location_street_number}),
100
),
# Add a whitespace, BLK, and another whitespace
" BLK ",
# Add the street name
${vw_report_offenses.offense_location_street_name}
)When you click [Save] the Table Calculation will be saved in the Custom Fields category and it will be added to your DATA pane.
Click [Run] to see the results.
This type of calculation can be combined with comparisons to only convert addresses in specific circumstances. Some examples are masking the address of sensitive locations, such as emergency housing for domestic violence victims, or the address of sensitive call/report types, such as sexual assault, child abuse, etc.