--- title: "IFBLANK Use Cases" slug: "ifblank-use-cases" description: "Learn how to efficiently use IFBLANK to automate data completion, merge columns, and enhance performance in your datasets with practical examples." updated: 2025-05-28T09:59:57Z published: 2025-08-22T12:00:18Z --- > ## Documentation Index > Fetch the complete documentation index at: https://kb.pigment.com/llms.txt > Use this file to discover all available pages before exploring further. # IFBLANK Use Cases `IFBLANK()` is used to plug gaps and automate the completion of patchy data. Here are some ways to use `IFBLANK()` efficiently, and explanations of some key features. ## Example 1: IFBLANK plugs gaps in Transaction Lists `IFBLANK()` is often used to complete high-density Blocks like Transaction Lists. Imagine the company WLGY Lemonade imports sales data into a Transaction List called `'Trans list'`, but the dates are spread across two or more columns. You know that column `'Date col 1'` is the most reliable, followed by `'Date col 2'`. To compile one definitive dates column that prefers data from `'Date col 1'`, you make a third, `'Merging 2 cols'`, containing the following formula: `IFBLANK('Trans list'.'Date col 1','Trans list'.'Date col 2')` ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/f9bc0685-cefa-4662-ba86-e3ca846336a6.png) > [!NOTE] > ℹ️ **Note** > > You can nest `IFBLANK()` expressions to merge successively more columns. The priority passes from `'Date col 1'` to `'Date col 2'` to `'Date col 3'` in the below example: > > `IFBLANK('Trans list'.'Date col 1',IFBLANK('Trans list'.'Date col 2','Trans list'.'Date col 3'))` ## Example 2: Using IFBLANK efficiently in low-density Metrics Large datasets are more performant [if you maximize sparsity](/v1/docs/how-pigment-handles-blank-cells). Use of `IFBLANK()` can quickly increase your density if you’re not careful, with an undesirable impact on performance. To keep `IFBLANK()` from overpopulating your dataset, you can combine `IF()` statements with `IFBLANK()`. For example, imagine WLGY Lemonade is giving performance-related bonuses to its employees. Those with qualifying appraisals have had their bonus manually input into the Metric already. The remaining Active Employees get a bonus of 10. Instead of using just `IFBLANK(Bonus, 10)`, which awards 10 to all employees in the current Metric, the modeler opts for `IFBLANK(Bonus, IF('Active Employee',10))` to keep inactive employees’ Items `BLANK`. This increases sparsity. > [!NOTE] > ℹ️ **Note** > > Even more performant is `IF('Active Employee',IFBLANK(Bonus, IF('Active Employee',10)))`, as it reduces further the number of computations required. ## Example 3: Implications of IFBLANK WLGY Lemonade collects sales data in a sparse Metric called `'Wk sales data'`: ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/e90ae161-a290-4cbe-aa8d-fc9db943f9be.png) It has a Metric that aggregates the data using the `REMOVE SUM` modifier. It applies the following `IFBLANK()` expression in the formula, replacing blanks with ones: `IFBLANK('Wk sales data',1)[REMOVE SUM: City,Product]` The output in the Metric is as follows: ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/ba9a1ecd-7397-4b03-97ba-6e39540652d7.png) The output has no ones, because: - `IFBLANK()` works at the granular level of its arguments, before modifiers are applied. - It has replaced all blanks in the Metric with ones, finding two blanks per city, across five cities in most cases. 1 x 2 x 5 = 10. - `IFBLANK()` retains the non-blank data, such as in week WC 2023-01-16, which has sales of 11 for product ‘Grenadine’ sold in city ‘Tartberry’. That week had nine blank cells, now filled with ones, plus one cell containing 11. This totals 20, as returned. And week WC 2023-01-30 had six blank cells filled with ones, plus sales of nine, totalling 15. - The ones becomes visible if you remove the `REMOVE SUM` aggregation: ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/277d93a5-cd89-4125-85ec-869b15a2d687.png) ## Learn more - [IFBLANK function](/v1/docs/ifblank-function) - [IF function](/v1/docs/if-function)