Formula to get part of a path

[janani_venkat]

Hi,

I know there is a reg replace function that can be used but not sure how to do that - below is what I need:

Data I have:

\\abc\abcd\abcde\MMYYYY\efg\ABC.xlsx|||Sheet1

Transformed data:

\\abc\abcd\abcde\MMYYYY\

What I am trying to do is define the output path based on input file path. So I would need part of the input path to which I will add another folder and save file based on MMYYYY info

Output path:

\\abc\abcd\abcde\MMYYYY\Controls\TestFile MM YYYY.xlsx

Can you please help me on how I can go about doing this?

Thanks for your help!

Jan

[clmc9601]

Hi @janani_venkat,

Here's a formula expression you can use. Replace the 1 in {1} with the number of subfolders you wish to remove from the path.

Regex_replace(FileGetDir([pathColumn]),'(.+)(?:\\[^\\]+){1}$','$1')

I hope this helps! If it does, please consider marking it as a solution so others may find it.

[clmc9601]

I believe the following would work as well, just nest as many FileGetDir() functions as subfolders you want to remove:

FileGetDir(FileGetDir([pathColumn]))

[DawnDuong]

hi @janani_venkat 

I am second-guessing your use case. Are you trying to save the workflows, inputs and outputs such that the path where the outputs are saved always in a named subfolder within the folder where the workflows and inputs are saved?

In that case, you can try to use "Relative Path".

Options > Advanced Options > Workflow dependencies.

I often use this method at work, by setting up a relative folder structure, which makes it easier to share/ save/ copy down.

The knowledge base article below discusses that in a bit more details.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Use-Relative-Paths/ta-p/1160

Dawn.

[atcodedog05]

Hi @janani_venkat 

Here is my take on it.

Workflow:

Hope this helps : )

[janani_venkat]

Hi All,

Sorry for comin back so late on this

@clmc9601 the input path will be browsed and selected via Interface tools like File and folder browse - is there anything I can use in that case?

@DawnDuong I should have been cleared - in the workflow, I allow users to select the input data and I want to be able to use whatever they selected to feed into my output path (i.e., read part of their path and save in a different folder under the same year-end / month-end date. Does that clarify? Is that even possible?

@atcodedog05 Unfortunately the path will keep changing based on what users select - how can I use the above in that case since the "path" is not pre-defined

[janani_venkat · Accepted solution]

Hi All,

Just an update to the above - I was able to figure out how to do this! 

If someone else stumbled here with similar query - please see below solution:

I used the "Interface" tool to get details of the source data and the path therefrom.

I then used append function to add the path to my excel data

When I then reached the output phase, I used this path to render my output 🙂