How to get First Non blank value from a line item which has no summary settings

Hi Everyone.,

I have a module that is dimensioned by Product List. Two line items are added, one is Product List formatted, while the other is text formatted(this is not dimensioned by Product). Product line item will have only one product at a time. I want that non blank value in "First non blank" line item. How to achieve this?

Accepted answers

All comments

KirillKuznetsov

@Saisri8568

Short answer is TEXTLIST() https://help.anaplan.com/c1e24970-400f-43af-95e7-b34a43253cdb-TEXTLIST

Or another version of it [TEXTLIST: ] I think the explanation of it much simplier here: https://help.anaplan.com/291bc437-9d30-48a4-a839-5246009fa0f2-x[TEXTLIST:y]

It works as a SUM, but for text. So you would need additional dummy list with a single element in it to use as your concatenation argument.

The following target module will take the value using [SELECT:Dummy list.Element]

ManjunathKN

Hi @Saisri8568

I dont think you can achieve that - the reason is simple
1. There are only 2 dimensions here - Product and time
2. You have only 12 cells left(no summary) when you remove product list - in text formatted line item

Alternate solution would be : -

to check on first occurrence and put a filter on "is not blank"
formula would be - "if isfirstoccurance(product, "your list in applies to") then name(product) else blank"
note -
1.You should have product list as dimension in text formatted line item

2."product" is your line item here in formula

Thanks,
Manjunath

rob_marshall

@KirillKuznetsov @Saisri8568

Please don't use TextList, there is a reason why there is a specific Planual rule for it (https://community.anaplan.com/t5/Chapter-2/2-02-11-Avoid-using-TEXTLIST/ta-p/80763)

Instead, please read through this thread as well at the one linked to it for ways to get around using TextList.

https://community.anaplan.com/t5/Anaplan-Platform/Uses-for-TEXTLIST/m-p/120721#M30238

Rob

KirillKuznetsov

@rob_marshall

Thank you for the correction and addditional solutions. Good to know!

JohnP

Hi @Saisri8568 ,

I've put a method below which uses FIRSTNONBLANK instead of TEXTLIST.

1. First you want to create a dummy list with a single list item. In my example I use a dummy list called 'S' which contains the single list item 'Pull'.

2. Then you add a line item e.g. "Product Pull" in your module formatted by this dummy list with the formula "IF ISBLANK(Product) THEN BLANK ELSE S.Pull ".

3. Then create a new module (called 'New Module' in this example) with your dummy list as the only dimension. Create a line item e.g. 'Firstnonblank Product', formatted by the Products list, with the formula "Product Module.Product[FIRSTNONBLANK: Product Module.Product Pull] ". This will display the first non-blank item from the 'Product' line item in your original module.

4. You can then use the 'Firstnonblank' line item in your original module to reference this line item, using the formula "New Module.Firstnonblank Product[SELECT: S.Pull] ".

While you should be wary of directly referencing individual list items and using the SELECT function, in this case it shouldn't cause too many problems as you're referencing a dummy list which has only one item.

(@rob_marshall pull me up on this though if I've got it wrong!)

Hope this helps!

Best wishes,

John

rob_marshall

@Saisri8568

Why not go the simply route and put a top level on the Product list and change th summary of that line item to FirstNonBlank?

@JohnP Kudos and a gold star goes to you for thinking out of the box, well done.

Rob

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…