How to force unique constraints in a List

Need a help. We are uploading the datafile into a General List. The code is supposed to be having unique values. But due to user error, we end up having duplicate values in the code column, causing issues in downstream process.

Is there anyway, we can do a check for duplicates during data file upload? If not, can you suggest some alternative solution?

Thanks

Regards

Prabakaran

Accepted answers

All comments

anirudh

Hi Prabakaran,

It's always best to fix data issues at the source only, if that's still an option...

Otherwise, follow these steps:
1. Create a text formatted line item with the values that are supposed to be unique. Ex: In your case, if the code was supposed to be unique and repeating twice, use the code line item
2. Create a boolean formatted line item called Unique with the formula:
ISFIRSTOCCURRENCE(Code, List Name of the module)

The 'Unique' will identify each row of data and will not contain duplicates

Let me know if you need more detail

Regards,
Anirudh

rob_marshall

@praba71

So @anirudh is correct on the first occurrence piece, but I am wondering how you are loading your list to have multiple "codes" which effect the downstream process. Can you show us the list you load the data to?

Thanks,

Rob

Kanishq17

hi @praba71 ,

you can use isfirstoccurence to find unique code as then make a filtered view and then load in the related downstream models which contain the list as a dimension

praba71

thanks @Kanishq17 , as @anirudh mentioned, I used the ISFIRSTOCCURRENCE to find out the duplicates on the particular column. This column may be blank also, inthat case ISFIRSTOCCURRENCE flags them as duplicate which may not be an ideal solution, is there any way, ISFIRSTOCCURRENCE considers only when there is data in the column and do the check for duplicates?

Please see the attached screenshot. Opsdb config is the column where we are checking for the duplicates.

Thanks

Regards

Prabakaran

Screenshot_2020-07-01_at_1_75627.png

anirudh

Hi Prabakaran,

Try this in another line item
IF ISBLANK(ColumnName) THEN TRUE ELSE First Occurrence?

Kanishq17

HI @praba71 s @anirudh mentioned use isnotblank(code) to identify nonblanks and then use Is first occurrence on those else keep true

Quick Links

Can you help?

Unanswered questions

Rolling forecast
Hi , I have a requirement for my stakeholder to have a medium term forecast (by month for the next 6 months) and a more detailed rolling 15-week sales forecast by day which will then feed into the wider Supply Chain planning processes over time. This needs to be updated by the Sales representants for consolidation by the…
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…