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

Amend hyperlink

adombayeva
7 - Meteor

Hello community,

 

Could you please help to solve this issues: 

 

1. The excel input file contains columns with hyperlinks, but when I open the file in the Alteryx, all hyperlinks shown as a text. How to make them visible as a hyperlink in the Alteryx output? 

 

2. Some hyperlinks require amendment, mainly partially removing/deleting the:  =HYPERLINK("  from the beginning of the hyperlink statement and from the end:    ","TD Chart"  . Could you please advise how to cut the hyperlink from the both ends.

Example: =HYPERLINK("http://login.GoodsReviews.com/public/ViewTdData/?goodsid=1907276486","TD Chart")

 

Thanks,

Anar

6 REPLIES 6
Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @adombayeva,

 

You could use a regex tool to solve this using an expression of:

 

=HYPERLINK\("(.*?)","TD Chart"\)

 

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

Regards,

Jonathan

adombayeva
7 - Meteor

Hello @Jonathan-Sherman,

 

Thanks for prompt respond,

 

Sorry if I was not clear before,

 

Your solution is exact for issue #2, but before that I wanted to make the hyperlink visible as a hyperlink but not as a text (TD Chart or TD Data under the columns "TD Chart" & "TD Data" in the Alteryx when I upload the Excel file into it.

 

Please find in the attachment:

 

1. Excel input file: please see the Alteryx Input Tab (this is how the input file looks like), Alteryx Output Tab (this is how the alteryx output file should look like afterwards).

 

PNG file: When I upload Excel as an input file and download Alteryx output file, none of them do not showcase the hyperlinks at all for me to make a corrections. 

 

Kind regards

danilang
19 - Altair
19 - Altair

hi @adombayeva 

 

This was a fun early morning exercise.

 

w.png

 

The key to this workflow is to import the .xlsx as zip file, and then read the contained xml files as csv with no delimiters, no field names and a long field length

 

input.png

 

Once you get worksheet and shared string xml files, its just a matter of parsing them to extract the rows and columns, and joining the shared strings to get the values.  This workflow has an extra step of parsing the hyperlink to extract the target.

 

The results look like this 

 

r.png 

Note: The Attached package contains your sample excel file with the Alteryx Output tab removed.  This was my first time parsing the internal structure of an Excel file and I wanted to start with a simpler file

 

Dan

adombayeva
7 - Meteor

Hello @danilang ,

 

Sorry for delayed response and thank you for your help! 

I am going to apply your workflow and let you know the soonest possible.

 

Best regards,

Anar

adombayeva
7 - Meteor

Hi @danilang,

 

I went through the proposed Alteryx workflow, at the end is it possible to leave columns C, D, E as an active hyperlinks having only the names like Data Type, Year & Country rather than a full string of hyperlink at each column?

Current output (snapshot from your workflow):

adombayeva_1-1579857217832.png

 

Desired output:

adombayeva_2-1579857275121.png

 

Thanks in advance,

Anar

N_FirstMileAnalytics
6 - Meteoroid

@adombayeva Any luck?

Labels