Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Formula to get part of a path

janani_venkat
7 - Meteor

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

 

6 REPLIES 6
clmc9601
13 - Pulsar
13 - Pulsar

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
13 - Pulsar
13 - Pulsar

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

FileGetDir(FileGetDir([pathColumn]))

 

DawnDuong
13 - Pulsar
13 - Pulsar

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
22 - Nova
22 - Nova

Hi @janani_venkat 

 

Here is my take on it.

Workflow:

atcodedog05_0-1631514818138.png

 

Hope this helps : )

 

janani_venkat
7 - Meteor

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
7 - Meteor

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 🙂

Solution.png

Labels