Property in List using a formula

I have a unique key list which is a standard naming convention of 12345_123456_123456.

I would like to use a formula to identify the last 6 digits that are not 000000 or 0000002 and create a list subset of those.

Can you help me figure out the formula for that? Thanks!!

Accepted answers

Misbah

@iWonder

I would write it this way

RIGHT('[List Name]'.[Property], 6) <> '000000' OR RIGHT('[List Name]'.[Property], 6) <> '000002'

Reasons: Your condition will process faster if you don't write IF ELSE statements with Boolean here.

Also Since Subsets are a miniscule part of the whole list TRUEs will be fewer than Falses - Planual Rule number 2.02 -17

I will also not hardcode the number to 6, instead will try to keep it dynamic by utilizing FIND, RIGHT & LENGTH functions.

Thanks,

Misbah

All comments

EarlC

I think this might work:

IF RIGHT('[List Name]'.[Property], 6) = '000000' OR RIGHT('[List Name]'.[Property], 6) = '000002' THEN FALSE ELSE TRUE

Might get you started in the right direction at least.

AWhitworth

Hi,

I think the above is the correct start, you can then import into your list module using your new line item to populate the subset for the list member. Alternatively, if you wanted a new list completely you could replace the TRUE FALSE conditions with RIGHT('[List Name]'.[Property], 6) and "" then import this into a new list.

Misbah

@iWonder

I would write it this way

RIGHT('[List Name]'.[Property], 6) <> '000000' OR RIGHT('[List Name]'.[Property], 6) <> '000002'

Reasons: Your condition will process faster if you don't write IF ELSE statements with Boolean here.

Also Since Subsets are a miniscule part of the whole list TRUEs will be fewer than Falses - Planual Rule number 2.02 -17

I will also not hardcode the number to 6, instead will try to keep it dynamic by utilizing FIND, RIGHT & LENGTH functions.

Thanks,

Misbah

rob_marshall

@iWonder ,

To add, why have this as a list property and not in SYS Module? By having this logic in a module, it will keep the logic in one SYS module where others will know to look for such data, b) you will have to create a subset action to set the subset which can be in this SYS Module (talks to maintenance) and c) this/these line items will show up in the model map whereas list properties don't.

Please try to stay away from using List Properties.

Thanks,

Rob

Alexandru.Rosca

@Misbah

I would use AND instead of OR in this instance. As it is written, the formula will return TRUE each time even if last digits are 000000 or 0000002.

parmod.kumar2

I also agreed on the use of AND instead of OR in this instance.

Please test

regards,

parmod

iWonder

Thanks!!

iWonder

Ok, I'll try to do that first

iWonder

Thanks!

iWonder

Ok thanks!

Quick Links

Can you help?

Unanswered questions

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…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…