Discussions

  Hi everyone,   More and more during my sessions with our customers, I am getting questions about utilizing existing python scripts with Alteryx Designer Python Code tool.   This especially whenever developers/ coders/ data scientists use some version of VC/ GIT to manage their Python code repos.   How can we automate a load of a py script into the Python Code tool in Alteryx Designer (de-facto inject the code into the tool)?     Note: As much as a would like to take all the credit for this one and say I spend many a sleepless night putting this together, this bit was actually created by the engineering team in Alteryx @PetrT    Oh wait - I did find this picture above. And created the macro around it... And tried to be (unsuccessfully) funny about it.   Here goes the workflow simple sample of a workflow utilizing that Python Script Runner/ Injector macro:   0/ The macro itself has two inputs, one for the file info (F), one for data (D) which is left optional 1/ Take FOLDER where your PY script is saved (could be relative or absolute) and FILE name of your script (PY format) 2/ Take DATA in the tabular format from Alteryx workflow to feed to the script (you can play with the macro to expand to multiple inputs) 3/ OUTPUT of the macro streams out the data from the Python Code tool     The macro itself is relatively simple, all that magic really happens in the injection script within that Python code tool     The code is here     # List all non-standard packages to be imported by your # script here (only missing packages will be installed) from ayx import Package #Package.installPackages(['pandas','numpy'])     from ayx import Alteryx df = Alteryx.read("#1") # Load the params from the input folder = "" #Placeholder for folder with a script script_file = "" #Placeholder for file with a script for index, row in df.iterrows(): folder = row[0] script_file = row[1].replace("\\","/") #print(folder) #print(script_file) from ayx import Alteryx import os import xml.etree.ElementTree as ET import re workflow_dir = os.path.normpath(os.path.normcase(Alteryx.getWorkflowConstant("Engine.WorkflowDirectory"))) try: temp_dir = os.path.normpath(os.path.normcase(os.environ['TEMP'])) except Exception as e: print('Cannot determine temp dir. ' + str(e)) temp_dir = None if workflow_dir is not None and temp_dir is not None: if os.path.samefile(workflow_dir, temp_dir): is_debug_workflow = True else: is_debug_workflow = False else: is_debug_workflow = None if is_debug_workflow: debug_workflow_path = os.path.join(temp_dir, 'debug_temp.yxmd') try: str_xml = open(debug_workflow_path, "r", encoding='utf-16').read() except Exception as e: print('Cannot parse debug workflow: ' + str(e)) str_xml = None if str_xml: workflow_xml = ET.fromstring(str_xml) if workflow_xml: for element in workflow_xml.iter('Node'): try: if element.find('GuiSettings').attrib['Plugin'] == 'AlteryxGuiToolkit.TextBox.TextBox': debug_text_box = element.find('Properties').find('Configuration').find('Text').text original_workflow = debug_text_box[ debug_text_box.find('<Module>') + 8:debug_text_box.find('</Module>')] workflow_dir = re.search(r'(.*)\\(.*)', original_workflow).group(1) except Exception: pass print('workflow_dir: ' + str(workflow_dir)) code_path = os.path.join(workflow_dir, folder, script_file) exec(open(code_path).read(), globals())     Actually, all that magic boils down to the last two lines which just simply load the PY script from your file system location using those FOLDER and SCRIPT FILE inputs.   Why all the code then? To fix this across situations like running the workflow in DEBUG mode, from TEMP location, etc.   What actually happens when you hit run in your DESIGNER? This code just simply finds&reads your PY script and executes it from the Python Code tool.   Note: You may wonder how my Iris Script I am running this way looks like, especially to handle the input data and output data connections. Notice that the macro is built now in such a way to get DATA from Connection #2. And write back is based again just how you define the Alteryx.write() function.     from ayx import Alteryx #read the iris dataset data = Alteryx.read("#2") #print out input data data #import scikit learn kmeans package from sklearn.cluster import KMeans #subset data to features used for clustering clusData = data [['SepalLengthCm','SepalWidthCm','PetalLengthCm','PetalWidthCm']] #fit subset feature to 3 clusters kmeans = KMeans(n_clusters=3, random_state=0).fit(clusData) print(kmeans.labels_) #import matplot lib import matplotlib.pyplot as plt #create scatter plot plt.scatter(clusData["SepalLengthCm"], clusData["SepalWidthCm"],c=kmeans.labels_) #add scatter title plt.title("Iris Clusters") #Write back the kmeans labels to the workflow downstream #First need to convert to Pandas DF import pandas as pd #labelsDf = pd.DataFrame(data=kmeans.labels_) data['labels'] = kmeans.labels_ Alteryx.write(data,1)       The sample workflow + code injection macro is attached. By all means, you can play some more with this and make it more to your liking.   Hope you find this useful 🙂   Big shout out to @PetrT again for trying to hide this one away from me for not that long actually. Cheers!   DM   ... View more

Hello, I need to log on to my Oracle database, using the Alteryx In-Database, using a proxy user. **When using a proxy user (Oracle), the right syntax looks like: proxy_user[owner_user].   The problem is that Alteryx does not allow to type more than 30 characters in the username. The length of my the proxy_user[owner_user] is more than 30 characters.   When I connect with the same parameters with other tool such as SQL Developer it works fine.   Any idea how could I connect properly with my parameters in Alteryx? Is there another option?   Thanks in advance! ... View more

We have below raw data - ID            Extension No    EXTENDS_ID   EXTENDED_BY_ID      EXPECTED Finish DATE 56,868                                                         67,204                           6/21/2019 67,204    1                         56,868               91,137                           2/5/2020 70,384                                                         80,472                          4/15/2020 76,075                                                         92,927                          8/15/2020 80,472    1                         70,384               86,063                         11/18/2020 86,063    2                         80,472               93,626                          3/11/2021 91,137    2                         67,204                                                    7/2/2021 92,927    1                         76,075                                                    8/11/2021 93,398                                                         96,202                          8/20/2021 93,626    3                        86,063                                                     8/25/2021 96,202    1                        93,398                                                     10/19/2021   ID are extended and have column how many times(Extension No) it has been extended as below - 1)     56868 - 67204 - 91137 2)     70384 - 80472 - 86063 - 93626 3)     76075 - 92927 4)     93398 - 96202   Requirement is.. We have to display highest ID number and lowest Finished date to its previous linked ID(initially created ID) and take expected finish date from 1st generated ID.   For example in in 1st scenario 91137 ID and Expected finish date from 56868 ID i.e. 6/21/2019.   Please help me to find out the solution for this in Alteryx. ... View more

Happy Halloween, Alteryx Community!   It's the spooky season, so to celebrate we're bringing back out traditional community digital costumes! For the weekend we're dressing up the profiles of anyone interested.   How This Works: Post here what you'd like your rank (the title below your username on a post) to say and what color you'd like your name to be, we'll update it so you can get your costume game going on all your posts.    We'll be accepting requests today and all 'costumes' will remain up till Monday November 2nd! ... View more

Hi Community,   I have a question: I would like to test if ID A has a "1" in one of the data columns (Typ A 2016 etc), is the the value in the following colums (years) also 1 or 0. Any ideas on how I could perform that analysis?   Thanks a lot in advance! ... View more