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

How to create identical child items for multiple parents
The data I'm trying to import is as follows Parent-Code A B A C B D C D D E I want to generate the following list from this data: L1 L2 L3 L4 A A-B A-B-D A-B-D-E A A-C A-C-D A-C-D-E You can create up to L3.However, L4 has multiple parents for one row of data When I try to create L4 from D-E, only one list row is generated…
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
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.…