Multi-Row Formula in Prep Builder

Version 2

    Tableau Prep Builder 2019.3 is currently in Beta and it has one new feature in particular I’m loving.

     

    You now have the ability to write Python or R scripts in your Prep workflow and expand the functionality of the software even further.

     

    Before I show you how to do a multi-row formula in Prep I need to mention the great work that Joshua Milligan, Tom Christian and  Sarah Battersby  have done in highlighting this feature.

     

    Firstly you need to set up Tabpy

     

    Installing and setting up TabPy is relatively straightforward but if you’re new to using it, follow the steps on GitHub here.



    In the 2019.3 version of Prep you’ll find a new option within your flow, ‘add script’.

     

     

    Here is what the configuration pane looks like for the script step.

     

     

    I’m connected to localhost.

     

    Browse to the script you want to run and type in the Function Name in the last part.



    Multi-Row Script

     

    Josh Milligan has written about Rank and TopN in a script and they are really useful scripts and Josh’s blogs are really easy to follow.

     

    I wanted to see if I could write a script that would return the value of the previous row depending on the value in another column.

     

    Here is the sample data I tested this on.

     

    For example I wanted to return the Time value for each occurance of 1 in the Value column.



    def Prev_Time(df):

     

      dataframe = pd.DataFrame()

     

      df['Prev_Time'] = df.loc[df['VALUE'].shift(-1)==1, 'TIME']

      df['Prev_Time'] = df['Prev_Time'].shift()

      return df



    You need to create the column ‘Prev_Time’ and ensure its the correct data type before you run the script. You can put any value in here as it’s just a placeholder.

     

    Here is a sample of the result;

     

     

    I should state that I’d never written any Python from scratch before this script, but a bit of googling and trial and error I ended up with the above script.

     

    What I like about this is how easy it would be to have a folder full of scripts to allow you to expand the functionality of Prep and also share these with colleagues.

     

    You can download the script file here;