-1

I have a table like this.

topic              pageviews

modelA/a.html        10
modelA/b.html        10  
modelB/a.html        10
modelB/b.html        20

And I want to add a directory column like this. It has a mapping from topic to directory.

topic              pageviews     directory

modelA/a.html        10           modelA
modelA/b.html        10           modelA
modelB/b/a.html      10            b
modelB/c/b.html      20            c
highbury
  • 1
  • 1

1 Answers1

0

Since column [topic] is different in your two examples I'm assuming it looks like the second one, and what you are asking is to add the third column. This can be done like this:

Create a calculated column in you data table:

directory = 
    var string = SUBSTITUTE([topic]; "/"; "|")

    return
    PATHITEM(
        string;
        PATHLENGTH(string )-1
    )

The variable converts the string by changing the '/' to a '|'. Then you can use all the PATH*-commands on that string. I have assumed that the directory you want is always the second last one, therefore I calculate how many items in the path and substitute one.

I end up with this: enter image description here

OscarLar
  • 1,315
  • 1
  • 5
  • 15
  • Thanks for your answer. But in fact, what I want is not always the second last. There is a mapping file. Sometimes we need second last, sometimes we need third last. Is there any method? – highbury Apr 02 '20 at 15:04
  • If you provide all relevant information to begin with it's much easier to help you. Please check this page: [HTO](https://stackoverflow.com/help/how-to-ask) – OscarLar Apr 03 '20 at 05:37