HU++: write conditions and values

What HU++ is

Most data preparation asks two questions of every row. Should this row stay? What should a new column say? HU++ is how you answer them in ConradBI. You write one line, and ConradBI works it out row by row.

There are two kinds of line:

• A condition is met or not met: [Status] = "Shipped".
• A value gives the row something to keep: [Units] * [Unit Price].

Each place you write HU++ asks for one kind, so you always know which one to write.

A line only reads the row in front of it. It runs no code and no loops, and it never changes your source. When your data comes from SQL Server, ConradBI turns the line into SQL and the server does the work. Otherwise it runs on your computer. Both give the same answer, so you write it once.

Coming from Excel or SQL? CASE does the job of IF, text goes in double quotes, and a missing value is [NULL]. The reference has a short table of equivalents.

Where you write it

The Row Filter and Custom Column steps work the same in a Stepped Transform Prep and in a Stepped Definition Grain. For DAR-C, see the DAR-C guide.

In a step or a dimension filter, a line with a problem changes nothing until you fix it. A typo never empties your data.

Before you start

You need a Stepped Transform Prep with its source element bound, so columns enter its steps. If nothing enters a step yet, the designer says so above the code window.

The examples in this guide use one small Orders table, the raw order data behind the Sales fact in the DAR-C guide:

Swap in your own column names as you go. The rest stays the same.

Your first condition: keep shipped orders

This takes about two minutes.

1. Open Prep, then your Stepped Transform Prep.
2. In Step Management, pick Row Filter from the list and click Add.
3. In the code window, type:

[Status] = "Shipped"

4. Open the Data tab. It shows the result up to the selected step: only shipped orders are left.

Read back, the line says: "Keep the row when its Status is Shipped."

Capitals don't matter, in names or in text. [status] = "shipped" keeps the same rows.

Tip: Pick Row Filter, not Filter Rows. Filter Rows is the simpler step, where you pick one column, an operator and a value. Row Filter takes any HU++ condition.

Your first value: a Revenue column

1. In Step Management, pick Custom Column and click Add.
2. In Column name, type Revenue.
3. In the code window, type:

[Units] * [Unit Price]

4. Next to Type, the designer says Gives Decimal: the type the value gives. Leave it, or pick another type and the value is converted to it. A value that won't convert is left empty.

The new column is added at the end. Steps after this one can use it as [Revenue].

A value can be more than math. It can be text, a date, a choice between values, or a condition, which gives a True or False column:

[Units] >= 100

How a line is built

A few rules make typing easier:

• Capitals don't matter in column names, commands or text comparisons.
• Type [ in the code window for a list of the columns and constants, and pick one instead of typing it.
• Where two elements meet, as in a Join Element step, name the element too: [Orders].[Region].

Conditions

Comparing

Numbers compare as numbers, dates as dates, and text without regard to capitals.

Joining conditions

AND needs both conditions met. OR needs either one. NOT turns a condition around.

[Region] = "East" AND ([Channel] = "Online" OR [Units] > 100)

AND is worked out before OR. Without the parentheses, this line would also keep every order of more than 100 units, from any region. Use parentheses whenever you mix the two.

In a list, or between two values

[Region] IN ("East", "West")

[Units] BETWEEN 10 AND 100

BETWEEN includes both ends. Put NOT before either one for the opposite: [Status] NOT IN ("Cancelled", "Test") or [Units] NOT BETWEEN 10 AND 100.

Matching a pattern

LIKE matches text against a pattern. [*] stands for any run of characters, and [_] for exactly one.

If you know SQL: % and _ are ordinary characters here. Use [*] and [_] instead.

For the everyday cases, CONTAINS, STARTSWITH and ENDSWITH read more easily:

CONTAINS([Email], "northwind")

Values

Math

+, -, * and / work on numbers, with * and / before + and -. Parentheses settle the order: ([Units] + 1) / 2.

Two things give an empty value instead of an error: a missing number on either side, and dividing by 0. [Cost] / [Units] is empty for an order of 0 units.

Dates don't take + or -. Move a date with DATEADD and count between dates with DATEDIFF, both in Dates.

Joining text

CONCAT([Region], " - ", [Channel])

This gives East - Online. A missing value adds nothing. Numbers come out as they are, and dates as 2026-03-14 00:00:00. For the date alone, take its first ten characters: LEFT(TEXT([Order Date]), 10).

Choosing with CASE

CASE gives the value of the first WHEN whose condition is met:

CASE WHEN [Units] >= 100 THEN "Large" WHEN [Units] >= 10 THEN "Medium" ELSE "Small" END

An order of 150 units is Large, 12 is Medium, and 5 is Small. The order of the WHENs matters, because the first one met wins.

ELSE is optional. Without it, a row that meets no condition gets an empty value.

In the code window, type CASE on a row and press Enter. You get a WHEN and a THEN to fill, + WHEN for another pair, and an ELSE while you edit the CASE.

When the values are of different types, the column takes a type that holds them all. A number and a piece of text give Text.

Missing values

A missing value, written [NULL], is the most common reason a line doesn't do what you expect. Three rules cover it.

1. A comparison with a missing value is neither met nor unmet. A Row Filter drops the row, and NOT doesn't bring it back. Where Discount is empty, [Discount] < 0.2 drops the order, and so does NOT ([Discount] < 0.2).
2. = [NULL] tests for a missing value, and != [NULL] for one that's there. [Ship Date] = [NULL] finds the orders that haven't shipped.
3. Math with a missing value gives a missing value. [Units] * [Unit Price] * (1 - [Discount]) is empty wherever Discount is.

COALESCE fills the gap with the first value that isn't missing:

[Units] * [Unit Price] * (1 - COALESCE([Discount], 0))

Lists work the same way. An order with no Status is neither IN nor NOT IN a list, so add it back when you want it:

[Status] NOT IN ("Cancelled", "Test") OR [Status] = [NULL]

[NULL] inside a list works too: [Status] IN ("Shipped", [NULL]).

Text and numbers

• Text compares without regard to capitals. "east" equals "East".
• Text that reads as a number compares as a number. If Code is a text column, [Code] < 10 is met by "9.5". Text that isn't a number, like "n/a", meets neither < nor >=.
• NUMBER turns text into a number. NUMBER([Code]) gives an empty value when the text isn't a plain number. "1,000" isn't one, because commas don't count.
• Spaces at the end don't count in LEN, and TRIM takes them off both ends.

The text functions are UPPER, LOWER, TRIM, LEFT, RIGHT, MID, LEN, REPLACE and FIND, all in the reference. Together they can take text apart. This gives the domain of an email address:

LOWER(MID([Email], FIND([Email], "@") + 1, LEN([Email])))

FIND gives where the @ is, MID takes what comes after it, and LOWER makes it small letters. BOB@Contoso.COM gives contoso.com.

Dates

Write a date with DATE, so it can't be misread as month/day or day/month: DATE(2026, 1, 31). Text next to a date is read in year-month-day order, so [Order Date] >= "2026-01-01" works too.

Take a date apart with YEAR, MONTH, DAY, QUARTER, WEEKDAY and WEEK. WEEKDAY counts from 1 for Monday to 7 for Sunday. WEEK gives the ISO week.

Move a date with DATEADD("unit", count, date). The unit is "day", "week", "month", "quarter" or "year", always in quotes:

DATEADD("month", 1, [Order Date])

A month after March 31 is April 30, because a month from the 31st lands on the month's last day. A negative count moves back.

Count between dates with DATEDIFF("unit", from, to):

DATEDIFF("day", [Order Date], [Ship Date])

It counts the boundaries it crosses. DATEDIFF("year", DATE(2025, 12, 31), DATE(2026, 1, 1)) is 1, though the dates are a day apart.

Go to the start of a period with STARTOF("month", [Order Date]), or "week", "quarter" or "year". A week starts on Monday. MONTHEND gives the month's last day.

Ask about today. A date condition needs nothing after it:

The reference lists all of them.

Today is the date of the computer doing the work: the SQL Server when the step runs there, and the processing computer otherwise. In a visual's Filters tab, it is the computer that draws the visual. Near midnight, two computers in different time zones can disagree by a day.

The code window

You write HU++ in a code window, not a plain text box. Each row holds one piece: a comparison, a value, AND or OR. A command like CASE, CONCAT, NOT or a function opens blocks under its row for its parts, and each block is shaded so you can see where it ends.

A few more things to know:

• Type or paste a whole line on one row. [Region] = "East" AND [Units] > 10 works on a single row. The next time the step opens, it is laid out one piece per row.
• Paste one line at a time. A row holds a single line, which is why every example in this guide is one line long.
• A CASE has its own controls. + WHEN adds a pair, ✕ takes one off, and the icon at the right of the CASE row deletes the whole CASE.
• Problems show under the code. The step runs once they are fixed. See When something goes wrong.

Recipes

Each recipe is one line, ready to copy. The first four go in a Row Filter step, the rest in a Custom Column step.

Keep this year's orders

IsThisCalendarYear([Order Date])

Keep the last 30 days

IsWithinLastDays([Order Date], 30)

Drop cancelled and test orders, but keep orders with no status

[Status] NOT IN ("Cancelled", "Test") OR [Status] = [NULL]

Find orders still open after a week

[Ship Date] = [NULL] AND DATEDIFF("day", [Order Date], [TODAY]) > 7

Revenue after discount

[Units] * [Unit Price] * (1 - COALESCE([Discount], 0))

Margin percent, to one decimal place

Add the Revenue column from your first value first.

ROUND(([Revenue] - [Cost]) / [Revenue] * 100, 1)

An order with no revenue gets an empty value, not an error.

Order size band

CASE WHEN [Units] >= 100 THEN "Large" WHEN [Units] >= 10 THEN "Medium" ELSE "Small" END

Shipping status

CASE WHEN [Ship Date] = [NULL] THEN "Not shipped" WHEN DATEDIFF("day", [Order Date], [Ship Date]) <= 3 THEN "On time" ELSE "Late" END

Days to ship

DATEDIFF("day", [Order Date], [Ship Date])

Empty until the order ships.

The month of the order, as a date

For a monthly grain or a monthly chart:

STARTOF("month", [Order Date])

A year and quarter label

CONCAT(YEAR([Order Date]), "-Q", QUARTER([Order Date]))

This gives 2026-Q1.

A date from a year

When a source gives only the year, end it on December 31:

DATE([Year], 12, 31)

Email domain

LOWER(MID([Email], FIND([Email], "@") + 1, LEN([Email])))

Product family from the code

LEFT([Product Code], 1)

When something goes wrong

Problems show under the code window. These are the ones you'll meet most.

Reference

Operators

Constants

Values and choices

Text

Numbers

Dates

Date conditions

Each takes a date and is met when the date falls in the window. A missing date isn't met.

IsThisRollingYear runs from this day a year ago to the end of today. IsPast means yesterday or earlier, and IsFuture tomorrow or later.

Types

A value has one of these types. The Custom Column step shows the type its value gives, and converts the value to the type you pick.

If you know Excel or SQL