Remove higher values

[ivoiculescu2020]

Dear team,

I need your help with these 2 situations:

1. I have this report that lists some documents and the versions we have in our systems. For example, the documents can have these versions: 1.A, 2.A, 3.A etc... However, I need to remove from the report any versions that are 2.A and higher. I used a Filter and it did the job. However, I need to know if it's an easier/more direct way especially because the versions will go much higher than 3.A.

2. I need to remove from the report any rows that contain certain words: for example, any row that contains "GB", "JP" etc. I used a Filter again to filter out these rows but I was wondering if there is an easier way because right now my workflow is quite messy and I am trying to clear it a little bit. Is there another function I could use to remove the rows that contain these "keywords"?

Many thanks in advance!

Kind regards,

Ioana

[atcodedog05]

Hi @ivoiculescu2020 

Can you provide sample data and expected output. We would be able to help you much better 🙂

[ivoiculescu2020]

@atcodedog05 Hello! My apologies.

Here it is a sample. I would normally use Filter multiple times to remove any rows that have GB, JP and any versions that are 2.A and higher. But the workflow looks long and messy. Is there a much direct way to remove the rows I don't need. The resulting report should only have these rows: 5, 6, 8 and 9.

Thank you!!!

[atcodedog05]

Hi @ivoiculescu2020 

You can use a formula like below

(REGEX_Replace([Document name / Version], ".+(\d+)\.A", "$1")<"2")

AND

(Contains([Document name / Version], "GB") OR
Contains([Document name / Version], "JP"))

First condition I am extracting and comparing the version number. Second condition I am check whether it has "GB" or "JP". I am using AND to check that both condition meets.

Workflow:

Hope this helps : )

[ivoiculescu2020]

@atcodedog05 many thanks for your quick assistance!

I need to remove any rows that have GB and JP AND any versions 2.A or higher. The 2 rows that result using your workflow actually show the rows that do have GB and JP....?

[Emil_Kos · Accepted solution]

Hi @ivoiculescu2020,

Depening if the list of the countries that you want to exclude I would pick two different approaches. If it is big or it is a short one I would pick one way or another. @atcodedog05 approach will work really well if you have a small list of countries that you want to exclude. 

If you have a big list of items that you want to filter out that or/additionally the list is changing in time I would add a find replace tool in your solution.

Please see my solution for reference. In the workflow, you can keep the list of the countries that you want to exclude and having a simple table like this can make your life easier in a long run. 

The downside of it is that the find and replace tool propably will be slower if you have a big amount of data but it is definitely easier to maintain. 

[Elias_Nordlinder · Accepted solution]

Hello @ivoiculescu2020 ,

(PS, did not see that @atcodedog05  already had a quicker solution with the filter tool directly,

so go for the solution below mine, but both works 😉 )

I think something like this would work for that, leveraging the regex tool.

Use the Regex Tool with the formula "(\d+)", it will parse out the digits from the string.

Use Filter tool to only filter on where this number is 1 and where the original string contains JP or CHN.

The result should be all the strings which only have the value 1 and is not JP or GB

[atcodedog05 · Accepted solution]

Hi @ivoiculescu2020 

My Bad. Fixed it

(REGEX_Replace([Document name / Version], ".+(\d+)\.A", "$1")<"2")

AND

(!Contains([Document name / Version], "GB") and
!Contains([Document name / Version], "JP"))

Workflow:

Hope this helps : )

[ivoiculescu2020]

@atcodedog05 @Elias_Nordlinder @Emil_Kos 

thank you all for your kind assistance! all your solutions work perfectly. The report is small for now (100-200 rows) but it has the potential to grow massively over time. So I am glad to have multiple solutions for both situations.

Have a wonderful day!