IIF - If condition apply correctly

In order to be able to apply the versatile uses and the correct composition of the IIF - If condition, we have compiled numerous examples from practice for you. In the first block, we introduce simple expressions. The second block is about so-called nested expressions and the last block is about practical standard cases, such as working with empty cells or date fields.

In our example we use a table with a customer master consisting of the columns Customer number as primary key and the further columns Postal code, Sales, Category and Potential.

The IIF - If condition always has the same structure, here the abstract form: IIF("condition", "true", "false"). An if-condition is set and the then-parameters are executed in the second part of the expression. Plamapatively, there is nothing else in the IIF - If condition than: if it rains (condition), then I take an umbrella (true), otherwise I put on sunglasses (false). So just follow the steps of the if-then logic and you should have less problems with the function.

Let's take a look at some examples using our table. If you click fx in the query dialog or in the table window and select a Calculated column, you will find the IIF - If conditionunder the Compare expression criteria.

In this section you will find simple expressions to get you started.

Example Characteristics should be countable

The category per customer is displayed in the customer master. Now the A, B, and C customers are to be made countable. So we need 3 columns with the corresponding characteristics, preferably a 1 if it applies and a 0 if not, are output.

If there is an A-customer in the column Category (condition), then there should be a 1 (true) in the cell, if not a 0 (false). IIF([category]= "A customer", 1 , 0 )

Example of categorization using a data column

In the customer master, A-customers should be defined by means of a threshold value. In this case, the sales column should be used for this purpose.

If the sales in the corresponding column are greater than 3,500 (condition), then A-customer (true) should be in the cell, if not expansion customer (false) IIF([turnover]>3500, "A-customer" , "extension customer")

Example If something is true, then the content of another column should be used

If a condition is met, then the content of another column can also be output. In this way, a selection can be made relatively quickly for the relevant categories in order to process them further.

If there is an A-customer in the column Category (condition), then the turnover should be taken from the column Turnover (true), if not then a 0 is set (false). IIF([category]="A-customer", [turnover], 0)

Example multiple categorizations using one column

Customers are to be divided into several categories by means of the turnover. Depending on how many categories I need I have to nest corresponding expressions one after the other.

If the turnover in the corresponding column is greater than 3,500 (condition), then A-customer (true) should be in the cell, if the turnover is greater than 2,000 (condition), then B-customer should be in the cell (true), if neither, then C-customer (false). IIF([Umsatz]>=3.500,"A-Kunde", IIF([Umsatz]>=2.000,"B-Kunde","C-Kunde"))

Example different characteristics are filled from several columns

The sales are in a separate column for each category and should be written into a single column for further calculations. This would be the content counterpart to the first example in the simple expressions, where everything from one column is split into multiple columns. The logic of the previous example can also be applied in this example and further nested depending on the number of criteria.

If there is an A-customer in the column Category (condition), then the turnover from the column Turnover A-customer should be taken (true), if not then the turnover from the column Turnover B-customer is taken (false). IIF([Category]="A customer", [Sales A customer], [Sales B customer])

In addition to the simple and nested expressions, there are a number of examples that can be assigned to one section or another, but should still be listed separately in terms of their meaning.

Example Fill empty cells with 0

Individual cells in the turnover do not have any values and are to be filled with 0 for further processing so that the columns are not ignored in calculations or even provide incorrect results.

If the turnover in the corresponding column is empty (condition), then 0.0 (true) should be in the cell, otherwise the turnover (false).

 IIF([turnover]==null, 0.0, [turnover])

Examples for working with date fields

In the first example, a planning area is defined between two days. Data fields can be used, for example, to determine whether contacts have taken place in a specified period.

If the visit day is less than 30 days and greater than 14 days (condition), return a 1 (true), otherwise return a 0 (false).

 Iff(DateDiff([visit day],date, cday)<30 and DateDiff([visit day],date, cday)>14; 1;0)

In the second example, it is determined whether the visit day is the same as the planned day. Depending on further use, texts or numbers can be output in these examples.

If the column VisitDay is empty (condition), then "not occurred" should be written in the cell (true), if there is a date in the VisitDay (false), then a comparison between the columns VisitDay and Plan_Date is made (condition), with the result "equal" (true) if the dates are identical and "unequal" (false) if the dates are different.

 IIF(ActualNull([VisitDay]), "not occurred",IIF([VisitDay]=[Plan_Date], "equal", "unequal"))

Examples of quality inspection

Here are two examples from quality inspection that are processed with nested expressions.

Here is an example with a nested comparison.

If the Quality column contains a value less than 0 (condition), then "too little" (true) should be output, if the Quality column contains a value greater than 0 (condition), then "very good" (true) should be output, otherwise "not good" (false).

 IIF([quality]<0, "too little" and IIF([quality]>0, "very good", "not good"))

Another example with a 3-way nested comparison.

If the Quality column does not contain (condition), then "fits" (true) is to be output, if the Quality column contains a value less than 0 (condition), then "too little" is to be output (true), if the Quality column contains a value greater than 0 (condition), then "very good" is to be output (true), if not then "not good" (false)

 IIF(ActualNull[Quality], "fits", IIF([Quality]<=0, "too little", IIF([Quality]>0, "very good", "not good")))

Example Determine visit days

To output visit frequencies, IIF - If condition can also be used. Here again a nested comparison has been used.

If the column Visits contains a 0 (condition), then output "no visit" (true), if the column Visits contains a value greater than 1 (condition), then output "once" (true), if the column Visits contains a value greater than 2 (condition), then output "two visits" (true), otherwise output "multiple visits" (false)

 IIF([visits]=0, "no visit", IIF([visits]>=1, "once", IIF([visits]>2, "two visits", "more visits")))

It happens every now and then that the perfect IIF - If condition unfortunately cannot be executed because something is wrong in the syntax. Here is a list of common errors:

  • Especially with nested expressions, the closing parentheses at the end should be checked.

  • Decimal places for number formats are separated with a dot, not with a comma as usual

  • Different data types cannot be processed. In this case, convert the data types to a common data type, for this you can use the expressions CText and CValue.

  • If a column is referenced, it is enclosed in square brackets.

  • The individual parts of the expression are separated by commas.