Need unique element through versions

Vladimir

Hi!

I've got a module with few dimensions, including versions, I generate code there for a list to update.

So I need to filter unique code. Currently, I use formulae:

IF RANK(1, ASCENDING, SEQUENTIAL, TRUE, 'List name') = 1 THEN 'List name' ELSE BLANK

But it does not work properly through versions (return duplicates)

Is there any other way to get unique elements within multiple versions?

I would appreciate any assistance!

Accepted answers

LipChean_Soh

Hi,

To be honest, the setup of Unique Codes should be done outside of Anaplan, since Anaplan is a client facing tool and not a database tool.

Having said that, please see if the following works.

1. Concatenate.All combines all All Entries.Code using TEXTLIST.

2. All Entries.'Count 2' counts the number of All Entries.Code in the entire list.

3. All Entries.'Unique Code 2' grabs All Entries.Code where All Entries.'Count 2' = 1

Screen Shot 2019-06-24 at 9.52.40 AM.png

Screen Shot 2019-06-24 at 9.52.29 AM.png

Thanks,

LipChean

All comments

LipChean_Soh

Hi,

I'll be helpful if you can create and share an excel mockup of what you're trying to achieve.

Thanks,

LipChean

Vladimir

Here is the file.

I need to filter unique elements in column CODE

1_47510.xls

LipChean_Soh

Hi,

If i just look at the first row, what are "BU", "UA", "2610", "1", "302066"?

Why is the Code for row 1 and 2 the same?

Why do you have different separators of _ and *?

Screen Shot 2019-06-22 at 6.24.19 PM.png

Thanks,

LipChean

Vladimir

It is the code, that is generated automatically and consist of few codes of other items such as Bussiness unit (bu_ua), product code and others, but this is not the matter.

The matter is that i need to filter unique codes throughout all dimensions including versions.

Thank you.

Vladimir

And the mater also is that there might be duplicates in code, so as I mentioned before, I need to filter unique ones

LipChean_Soh

Hi，

What do you mean by 'filter unique codes throughout all dimensions including versions'?

Perhaps you can show the expected end results in another screen shot?

Thanks,

LipChean

Vladimir

Here is the file, please consider

1_47518.xls

LipChean_Soh

Hi,

Screen Shot 2019-06-23 at 9.13.10 AM.png

There are 2 lists, All_Entries corresponds to your longer list; Alphabets corresponds to unique members created from your longer list.

Screen Shot 2019-06-23 at 9.13.32 AM.png

The 'Code' line item is of 'Text' format, this is used to create the 'Alphabets' list through import action.

The rest of the formulas are shown.

Screen Shot 2019-06-23 at 9.13.42 AM.png

Thanks,

LipChean

Vladimir

Hi, thank you, but this won't work for me.

Because this will do if all letters are in the list 'Alphabets' (you count letters in the list)

if I import new data (letters F,G,H...), code(List) column will be empty for new letters.

I need to filter new unique letters(F,G,H...) that are not in the list yet

LipChean_Soh

Hi,

You can perform 2 imports sequentially, i.e. the first from whatever source into 'All Entries', and then the second from 'All Entries' into 'Alphabets'.

You can incorporate a delta mechanism, such that previously imported 'All Entries' are flagged, and not imported again from 'All Entries' into 'Alphabets'.

Thanks,

LipChean

Vladimir

Yes, 2 sequential imports will do.

But I hoped to solve with formulae because I have too many imports and a huge amount of items in the model, so duplicating imports will affect Its speed

LipChean_Soh

Hi,

To be honest, the setup of Unique Codes should be done outside of Anaplan, since Anaplan is a client facing tool and not a database tool.

Having said that, please see if the following works.

1. Concatenate.All combines all All Entries.Code using TEXTLIST.

2. All Entries.'Count 2' counts the number of All Entries.Code in the entire list.

3. All Entries.'Unique Code 2' grabs All Entries.Code where All Entries.'Count 2' = 1

Screen Shot 2019-06-24 at 9.52.40 AM.png

Screen Shot 2019-06-24 at 9.52.29 AM.png

Thanks,

LipChean

DavidSmith

@Vladimir

Maybe I am missing something, but if you create a list based on the code, that will only generate unique values (you can ignore the duplicate value errors)

You can then bring the data into a module by country, list, version and the list created above

David

Vladimir

Hi David.

This is exactly what I'm trying to avoid, many imports in model generating duplicate value errors, so I need to form views with unique elements.

Vladimir

Hi,

That is tricky)

But it works.

Many thanks!!!! @LipChean_Soh

DavidSmith

A better solution is to try and get the file sorted at source and have only the unique members in the file

Can you ask to provider of the file to do this? It will simplify the problem because Anaplan is not an ETL tool

David

LipChean_Soh

@Vladimir

I agree with @DavidSmith, i.e. "Can you ask to provider of the file to do this? It will simplify the problem because Anaplan is not an ETL tool"

Remember, just because we can do it, doesn't mean we should do it.

As mentioned at the beginning of my suggestion, the setup of Unique Codes should be done outside of Anaplan, since Anaplan is a client facing tool and not a database tool.

Thanks,

LipChean

Vladimir

I'm not sure if the customer's IT dept would be able to generate these codes, but I'll try

Thanks again.

Quick Links

Can you help?

Unanswered questions

Prevent end users deleting a list item when using Worksheet in UX.
Hi, I have a worksheet for end users to enter new hires into, for which the list item which persist across versions. I am having an issue where the end user can delete a list item using the grid functionality because the Current Version value is blank, but I want to prevent them deleting them (as data will be lost from…
Introducing Anaplan API Python SDK
Hey everyone, I wanted to share the Anaplan Python SDK with you. It's a Python Library that wraps all Anaplan APIs and makes it easier to interact with Anaplan programmatically. It is mostly designed with Data Integration Scenarios in mind, but it does support all APIs including ALM, SCIM, Audit, and Cloud Works. Please…
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…