topic Equivalent to LOOKUP Function but for a Text formated Line Item? in Anaplan Platform
https://community.anaplan.com/t5/Anaplan-Platform/Equivalent-to-LOOKUP-Function-but-for-a-Text-formated-Line-Item/m-p/675#M3205
<P>Hi All, I have an Import Module which has a Number and a Text Line Item. I have created another module with the Dimensions <STRONG>Provided Service</STRONG> and <STRONG><STRONG>Cost Centre </STRONG></STRONG>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<STRONG> 'Import 3 - Consumption Data'.Consumption Volume[SUM: 'Import 3 - Consumption Data'.'Provided Service - Lookup', SUM: 'Import 3 - Consumption Data'.'Cost Centre - Lookup'] </STRONG>I now want to do the exact same thing but returning the Text formatted line item I thought this formula would work<STRONG> 'Import 3 - Consumption Data'.Consumption Unit[LOOKUP:'Import 3 - Consumption Data'.'Provided Service - Lookup', LOOKUP:'Import 3 - Consumption Data'.'Cost Centre - Lookup'] </STRONG>but it appears LOOKUP: can only be used on Amounts and not Text is there an equivalent function for Text? Many thanks Paul</P>Tue, 30 Oct 2018 20:56:49 GMTPrevContributor2018-10-30T20:56:49ZEquivalent to LOOKUP Function but for a Text formated Line Item?
https://community.anaplan.com/t5/Anaplan-Platform/Equivalent-to-LOOKUP-Function-but-for-a-Text-formated-Line-Item/m-p/675#M3205
<P>Hi All, I have an Import Module which has a Number and a Text Line Item. I have created another module with the Dimensions <STRONG>Provided Service</STRONG> and <STRONG><STRONG>Cost Centre </STRONG></STRONG>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<STRONG> 'Import 3 - Consumption Data'.Consumption Volume[SUM: 'Import 3 - Consumption Data'.'Provided Service - Lookup', SUM: 'Import 3 - Consumption Data'.'Cost Centre - Lookup'] </STRONG>I now want to do the exact same thing but returning the Text formatted line item I thought this formula would work<STRONG> 'Import 3 - Consumption Data'.Consumption Unit[LOOKUP:'Import 3 - Consumption Data'.'Provided Service - Lookup', LOOKUP:'Import 3 - Consumption Data'.'Cost Centre - Lookup'] </STRONG>but it appears LOOKUP: can only be used on Amounts and not Text is there an equivalent function for Text? Many thanks Paul</P>Tue, 30 Oct 2018 20:56:49 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Equivalent-to-LOOKUP-Function-but-for-a-Text-formated-Line-Item/m-p/675#M3205PrevContributor2018-10-30T20:56:49ZRE: Equivalent to LOOKUP Function but for a Text formated Line Item?
https://community.anaplan.com/t5/Anaplan-Platform/Equivalent-to-LOOKUP-Function-but-for-a-Text-formated-Line-Item/m-p/676#M3206
Hi Paul,
Great question. The answer is YES :D
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:<UL>
<LI>FIRSTNONBLANK - grabs the first string that it finds
</LI><LI>LASTNONBLANK - returns the last string that is finds
</LI><LI>TEXTLIST - concatenates all the text that it finds with a comma (,)
</LI></UL>https://community.anaplan.com/anapedia/calculation-functions/firstnonblank
https://community.anaplan.com/anapedia/calculation-functions/textlist
So I think in your case your formula would be: <PRE>'Import 3 - Consumption Data'.Consumption Unit[FIRSTNONBLANK:'Import 3 - Consumption Data'.'Provided Service - Lookup', FIRSTNONBLANK:'Import 3 - Consumption Data'.'Cost Centre - Lookup']</PRE>
Post back and let me know how you get on. B)
Kind regards,
SimonWed, 09 Jul 2014 19:34:53 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Equivalent-to-LOOKUP-Function-but-for-a-Text-formated-Line-Item/m-p/676#M3206simon_ritchie2014-07-09T19:34:53ZRE: Equivalent to LOOKUP Function but for a Text formated Line Item?
https://community.anaplan.com/t5/Anaplan-Platform/Equivalent-to-LOOKUP-Function-but-for-a-Text-formated-Line-Item/m-p/677#M3207
Perfect, thanks Simon thats doing exactly what I wanted.
Cheers
PaulWed, 09 Jul 2014 19:44:13 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Equivalent-to-LOOKUP-Function-but-for-a-Text-formated-Line-Item/m-p/677#M3207PrevContributor2014-07-09T19:44:13ZRe: RE: Equivalent to LOOKUP Function but for a Text formated Line Item?
https://community.anaplan.com/t5/Anaplan-Platform/Equivalent-to-LOOKUP-Function-but-for-a-Text-formated-Line-Item/m-p/46028#M5646
<P>This was a huge help! Thanks Simon</P>Wed, 22 May 2019 15:35:33 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Equivalent-to-LOOKUP-Function-but-for-a-Text-formated-Line-Item/m-p/46028#M5646TGagnon2019-05-22T15:35:33Z