Tagging Duplicate Items
Hi All,
I was hoping to get some with a problem that I'm trying to solve. I have a module with a line item that is list formatted. In this module, the 'applies to' is a list that has 7 values. The line item in the module has the following items shown for each of the 7 rows:
item 1: Product A
item 2: Product B
item 3: Product B
item 4: Product C
item 5: Product C
item 6: Product C
item 7: Product D
I'm trying to identify items that are shown more than once and create an additional snippet of text at the end to effectively make each item completely unique. The output I'd like to see is:
item 1: Product A_1
item 2: Product B_1
item 3: Product B_2
item 4: Product C_1
item 5: Product C_2
item 6: Product C_3
item 7: Product D_1
NOTE: I do not want to set this up with a series of isfirstoccurrence logic that would require for me to setup a bunch of line items to accommodate any number of duplicate results. This logic needs to work with any number of duplicate results.
Can anyone solve this for me?! Thank you!
Best Answer
-
@jbrass Great question!
I think there's lots of ways to solve this but the one that comes to my mind is just to use the RANK function.
This will generate a Rank # within your products (make sure to use the SEQUENTIAL so you get a unique number in case of tie)
Then concatenate the product and rank together.
That should generate a unique ID for you.
2
Answers
-
@JaredDolich is correct and the way I would go. Not only use the SEQUENTIAL parameter, but also the grouping so the numbers start over by Product (in your case).
Quick question, what is the use case you are trying to solve for by making the list members unique? Usually, we are fighting that battle from the other way.
Rob
0 -
Thank you both. Still struggling with the RANK function a bit. The first input in the function is the source data, which has a requirement of being a number, date, or time period. What should I point this first parameter to?
Rob - in regards to your question: I am trying to ingest a data source that does not include a unique ID. This is going to come as a manual file load (for now), and therefore there is no ETL layer that I can leverage to source a unique ID. I'm OK with manually populating a basic unique ID in the source file (by just including a "row()" formula in excel), but need a mechanism by which we can clear and reload portions of the data as needed. So, I need an ID that I'll be able to replicate next time I load the data, and have Anaplan know which items to replace vs. add to the list. I was going to use the example above to accomplish that. My example was obviously very much simplified, and the real example would actually be a concatenation of SFDC opportunity and product. Hope this makes sense, and open to feedback.
Thanks,
Josh0 -
Update - figured out the rank function. Thanks so much! Rob - still open to discuss the approach.
0 -
@jbrass ,
Granted it has been a long time since I have played with SFDC, but I believe you can use the Opportunity ID (Record ID - 18 digit) from SFDC to ensure the opportunities are unique.
Rob
0 -
My extract has multiple lines per SFDC Opp ID, so I'd need to get a unique ID for that Opp ID/Product ID combination, and I'm working the SFDC guy in house to see if we can get that. Was working on this as a backup method in case we can't get that.
Anyway, this works! Thanks for your prompt help!
1
