Skip to main content

Hi Pigment Community!

I am struggling with a problem and was wondering if any of you could help me solve it. 

The Issue: I'm working with a transaction block imported from Excel in Pigment, and unfortunately, not all the data is filled in. Specifically, I'm dealing with the Country/Region column in my All Deals transaction block. This column is dimensioned by Country Mapping, which, in turn, is connected to the Country dimension (screenshots attached for context). The snag I'm hitting is figuring out how to apply a formula that allows me to specify what information from my dimension to use when populating blank cells.

The Goal: I aim to create a function in my Country/Region column that fills in blank cells with the dimension "Missing Data" for easy identification later on. The idea is to check the cell contents – if the cell is blank, I want to populate it with 'Missing Data,' sourced from my Country Mapping dimension. If the cell is not blank, I want to leave it unchanged. For instance, "France" would stay as is, but a blank cell would become "Missing Data." Additionally, I need this functionality for future modeling, so simply using filters to identify blank instances won't be sufficient.

 

The formula I have been trying to use is : 

IFBLANK('All Deals'.'Country/Region', 'Country mapping'.Countries."Missing Data")

I appreciate any insights or guidance you can provide. Screenshots of my dimensions are included below for better understanding.

All Deals - Transaction block (country/region is dimensioned on country mapping)

 

Country Mapping - Dimension Block to make all countries in English language

 

Country Dimension - has country specific information
This is the formula I am trying to use but it is incorrect

 

Thank you in advance for your assistance!

Hi @darious ,


Your formula is generating a circular reference since you are referring to the same property where you adding the formula.
Could you create an new property and apply the formula to avoid the circular reference:

​​​​​​​IFBLANK('All Deals'.'Country/Region', 'Country mapping'."Missing Data")

It should work without referencing Countries.

Hope this helps,
Issam

Hi Issam, 

 

Your solution worked perfectly, thank you!

Hi @Issam Moalla,

I can follow your solution and it works but I’m wondering if this is the best practice? If I do this for all my dimensions in my transactional data import than I’m doubling the number of dimensions to avoid the circular reference.

Isn’t there a more convenient way of doing this?

Best,

Alex

Reply


Ask your question here!

Terms & ConditionsCookie settings