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.
Thank you in advance for your assistance!
Best answer by Issam MoallaView original