Any alternative for the TEXTLIST() function?

aykcos

Hi all,

 

The use of the TEXTLIST() is not a best practice. However, as I go through the Anapedia, Planual and forum threads I haven't seen the alternative for that. There are some forum threads that provide ANY, FIRSTNONBLACK, LASTNONBLACK as alternatives for the TEXTLIST[], which is aggregation function. But I need alternative to the TEXTLIST() function, which is a TEXT function, not an aggregation function.

 

I'd appreciate your replies? Thanks in advance.

KirillKuznetsov

@aykcos 

Hi, you are completely right about the best practices. TEXTLIST() function is very limited in cases where it cannot be avoided and replaced.

But it always depends on what your case is. Could you please provide what you are trying to achieve and we will try to help you choosing the right solution?

aykcos

Hi @KirillKuznetsov ,

Thanks for your reply. IF 'SYS57 Territory FL'.Code = CODE('???QCALC08 Account Coverage Report'.Territory Assignment) THEN TEXTLIST('???QCALC08 Account Coverage Report'.Portfolio Text List, ", ", 'P4 Portfolio Group') ELSE BLANK

For example the formula that I am trying to optimize is TEXTLIST(Module A.Line Item B, ",", 'List C') . What I'd like to know is, is there a alternative way to get the same result?

Thanks in advance.

KirillKuznetsov

@aykcos 

Could you please send the blue prints (including Applies to info) of the following:

1)Target module where the formula have to be applied.

2) QCALC08 Account Coverage Report module

Also do you expect that there will be one item within textlist function result. Or the requirements say that there may be multiple Portfolio Text List per cell?

 

aykcos

Here is the blueprint view of the target module.

And here you can find the blueprint view of the source model.

 

I really appreciate your help Kiril.

Thanks.

KirillKuznetsov

@aykcos 

Do you match elements from Territory FL list to elements from Portfolio Group list anywhere? Is it Territory assignment line item?

Do you want to show which Portfolios are assigned to Territory FL list in the target? If yes -  I would recommend you to add the Portfolio Group dimension in the target and use filter to show only those Portfolio elements which are applied to this territory. This way you would have a matrix of L1 Group,  Territory FL and Portfolio Group. The data will be clear and easy to reference for further possible calculations.