Ciaren

Blog

When Blank Values Should Trigger a Fallback

Choose a fallback column using a ticket-routing example. See how nulls, empty strings, and whitespace affect which source value wins in each row.

About this post

Published
Reading time
2 min
Author
Ciaren team
data-cleaningnull-valuescoalesce

During a help desk migration, each ticket has a current_queue from the new system and a legacy_queue from the old one. The current queue takes precedence. When the new system has no assignment for a ticket, the old queue can fill the gap. A blank current queue needs a decision first. It might be an export artifact, or it might mean an agent deliberately cleared the assignment.

Decide what an empty field means

Coalesce Columns checks each row in priority order and picks the first non-null value. The empty string "" counts as a value, as does a string containing only a space. If an empty string records a deliberate clear, leave it in place so the old queue stays out of that row. If your exporter uses blanks for unassigned tickets, trim the values and turn empty strings into nulls before choosing a queue. Coalesce Columns

Ticketcurrent_queuelegacy_queueCoalesce as importedCoalesce after treating blanks as missing
T-101billinggeneralbillingbilling
T-102nullreturnsreturnsreturns
T-103""billing""billing
T-104" "general" "general
T-105nullnullnullnull
T-106null" "" "null

Read "" as empty text and " " as one space. Look at the values after import, because a reader can interpret empty fields as null. If the file uses the same representation for a deliberate clear and a missing assignment, ask for a separate status field or an export that preserves both states. The fallback can only act on distinctions present in its input. File Input

Keep the input columns during review

If a blank should block fallback, point Coalesce Columns at current_queue, then legacy_queue, and write routing_queue. To apply the blank-as-missing policy in a pandas flow, add a Python Transform before the coalesce step. Use the following as the function body. It creates comparison columns and leaves the imported values available for review. Python Transform

python
current = df["current_queue"].astype("string").str.strip()
legacy = df["legacy_queue"].astype("string").str.strip()

return df.assign(
    current_candidate=current.replace("", pd.NA),
    legacy_candidate=legacy.replace("", pd.NA),
)

With this step in place, set columns in Coalesce Columns to ["current_candidate", "legacy_candidate"], new_column to routing_queue, and leave keep_original on. Preview the columns side by side. T-101 checks whether the current queue wins when the two systems disagree, T-103 checks the rule for empty text, and T-106 checks that a blank in the backup is handled as well. Coalesce fallback recipe

Leave routing_queue null when neither source supplies a queue. In this migration, those tickets go to an exception list for review. Sending them all to general would make it harder to tell which tickets genuinely belong there.