Issue with Unit Mapping from JSON Using Macro and Find & Replace in Alteryx

[Shaunak18]

Hi All,

I'm facing a challenge in Alteryx related to data transformation from a JSON input file, specifically around unit mapping using a macro and the Find & Replace tool.

Use Case:

The JSON file contains data under the category alcohol, with entries like "6–8 drinks". I’ve implemented logic to extract:

• Quantitative value: the maximum value from the range (e.g., 8)
• Qualitative unit: the text portion (e.g., "drinks")

We are using a macro and a Find & Replace tool to map units from a data dictionary. The expected behavior is:

• If the unit (e.g., "drinks") is not present in the data dictionary, it should return Null().
• If the unit is added later (e.g., "drinks" mapped to "bottle"), it should dynamically return the mapped value.

Current Formula Used:

[JSON_Name] +
IF [Name] = "Quantitative" THEN ".value"
ELSE ".unit"
ENDIF

below are the one tested 

1) condition tired-

IF Contains([Name], "drinks") THEN [JSON_Name] +
IF [Name] = "Quantitative" THEN "-value"
ELSE "-unit"
ENDIF
ELSE Null()
ENDIF

2) formula tried -

IF Contains([Name], "drinks") THEN Null()
ELSE [JSON_Name] +
IF [Name] = "Quantitative" THEN "-value"
ELSE "-unit"
ENDIF
ENDIF

3) formula tried

IF Contains([Name], "drinks") THEN Null()
ELSE [JSON_Name] +
IF [Name] = "Quantitative" THEN "-value"
ELSE "-unit"
ENDIF
ENDIF

Issue:

Even when "drinks" is not present in the data dictionary, the output still shows "drinks" as the unit. This is incorrect based on our requirement — it should return Null() unless the unit is explicitly mapped.

When "drinks" is added under the "bottles" category in the dictionary, the output correctly shows "bottle", which is expected. However, if "drinks" is removed from the dictionary, the macro still returns "drinks" as the unit, which violates the intended logic.

Expected Behavior:

• The macro should only return units that are matched from the data dictionary.
• If a unit is not found (e.g., "drinks" is removed), the output should be Null().
• The transformation should be dynamic — if "drinks" is added back and mapped to "bottle", it should reflect that change.

if there would have 8-9 canister then the unit would have been bottles else null same for drinks

bottles

Bottle

canister

canisters

Container

Containers

flask

flasks

jar

jars

jug

jugs

Vessel

Vessels

vial

vials

drinks

[KGT · Accepted solution]

Are you trying to use the Find/Replace Tool with Replace being the function? And then, if the term is not found, it currently leaves the data as is, but you want it to be Null() if it's not found? I'm not sure where the JSON comes into it, or pulling out the other values...

If that's the case, then:

The Find/Replace Tool will not replace anything that it doesn't find, including not removing anything that doesn't match. If you want to use this method, try using the Append function in the Find/Replace, and that will give you a new field with your desired results. You can rename this field to replace your old one if you would like.

[Shaunak18]

.

[Shaunak18]

.