Community perspective: IFO – UPPER and LOWER to the rescue
IFO is a very crucial formula to use when working with raw data, staged in Data Hub, to create lists from within the data's metadata. It truncates the metadata within its line item to "Its First Occurrence" to prevent duplicates being imported into its target list. This keeps the action results clean and optimizes performance.
A challenge imposed to IFO is inconsistent data, where a unique ID, to which IFO is applied, has different instances of lower and upper case letters. IFO is case sensitive. If a unique ID has at least one character capitalized in one instance and lower in another, then IFO will see them as unique from each other and display both.
The example below demonstrates staged data being used to create a new UID downstream by combining customer and SKU together in ‘Create UID.’ The first five lines of the examples has the same customer and SKU combination, but the SKU description is inconsistent with the way it applies upper/lower cases to its characters: Maroon, MAROON and maroon. IFO does not recognize these first five lines as all being the same.
When you execute the action to import this data into a list then you'll get errors pointing out that two or more of the same values were imported and ignored.
Below is the saved view being used to create the UID list. The items circled are identical except for application of upper/lower case characters.
Below are the results of importing the saved view (shown above) into a list. The list treats the first three items as being identical, even though IFO does not, and rejects the duplicates with the error ‘Another row has already been processed with this key.’
The work around for this is to create another line item to apply UPPER or LOWER to the line item's data, with the issue described above, and then use that line item to embed within the IFO formula. This will force the characters to conform as the same (as lower or upper, whichever you choose) and allow IFO to accurately limit the data to unique items.
The example below demonstrates what’s described above. I have a new line item to apply the UPPER function (‘UPPER_Create UID’) to the line item creating issues (‘Create UID’). I have a new line item for IFO (‘IFO_Upper Create UID’), and kept the original IFO line item to compare results. The new IFO line item (‘IFO Upper Create UID’) references the UPPER line item (‘UPPER_Create UID’) which applies the UPPER function to create consistency across all the string’s characters. Now that the characters are consistent, the IFO function will NOT pull duplicate items.
When I import the saved view, I still load the ‘Create UID’ line item to the list as a code or description to prevent having the string in all capital letters. I just have the UPPER line item to support the IFO function to disregard duplicates due to IFO’s lack of case sensitivity.
The data we have to work with is never perfect and fixes at the source isn't a timely guarantee. Where Anaplan can help, it should!