Equivalent to LOOKUP Function but for a Text formated Line Item?
Hi All, I have an Import Module which has a Number and a Text Line Item. I have created another module with the Dimensions Provided Service and Cost Centre and in the Import Module created List fomatted Line Items for those 2 Dimension. I have successfully pulled across the Number formatted Line item from the Import Module using the SUM: function. This is the formula in the target module 'Import 3  Consumption Data'.Consumption Volume[SUM: 'Import 3  Consumption Data'.'Provided Service  Lookup', SUM: 'Import 3  Consumption Data'.'Cost Centre  Lookup'] I now want to do the exact same thing but returning the Text formatted line item I thought this formula would work 'Import 3  Consumption Data'.Consumption Unit[LOOKUP:'Import 3  Consumption Data'.'Provided Service  Lookup', LOOKUP:'Import 3  Consumption Data'.'Cost Centre  Lookup'] but it appears LOOKUP: can only be used on Amounts and not Text is there an equivalent function for Text? Many thanks Paul
Best Answer

Hi Paul,
Great question. The answer is YES
Try [FIRSTNONBLANK: ] instead of [SUM: ] when you are working with text.
As you've seen, when you are working with numbers, it's possible to , and also , , .
Because text can't be aggregated like numbers can, with text you have a couple of different options: FIRSTNONBLANK  grabs the first string that it finds
 LASTNONBLANK  returns the last string that is finds
 TEXTLIST  concatenates all the text that it finds with a comma (,)
https://community.anaplan.com/anapedia/calculationfunctions/textlist
So I think in your case your formula would be:'Import 3  Consumption Data'.Consumption Unit[FIRSTNONBLANK:'Import 3  Consumption Data'.'Provided Service  Lookup', FIRSTNONBLANK:'Import 3  Consumption Data'.'Cost Centre  Lookup']
Post back and let me know how you get on.
Kind regards,
Simon3  FIRSTNONBLANK  grabs the first string that it finds
Comments

Perfect, thanks Simon thats doing exactly what I wanted.
Cheers
Paul0 
This was a huge help! Thanks Simon
0