3 Replies Latest reply on Nov 19, 2018 9:48 AM by Joshua Milligan

    Using Prep as a Replacement for an Excel Macro

    Rory Wallis

      Hi all,

       

      I am currently working on automating scheduled refreshes of Tableau Prep flows. The process involves a direct database connection, with some manipulation in Prep and then a .hyper output at the end. When testing yesterday, I managed to get this all working (hooray for saving myself a few hours of work a week!).

       

      As good as this is, there is a problem that I'm about to come across that I don't know how to approach. The nature of our sales data is that we will have product lines with other lines for additional costs. As a general rule, the supplier for the product will be listed but will be absent for the additional costs. We work on the assumption that the add costs supplier was the same as the product supplier unless otherwise stated. We usually have to apply this logic in a macro after the data has been extracted. As a visual guide the macro will do the following:

       

      Original data:

       

      Order 1     Supplier 1          Product          Item Number 1

      Order 1     No Supplier       Add Cost        Item Number 2

      Order 1     No Supplier       Add Cost        Item Number 3

      Order 2     Supplier 2          Product          Item Number 1

      Order 2     No Supplier       Add Cost        Item Number 2

      Order 2     Supplier 3          Product          Item Number 3

      Order 2     No Supplier       Add Cost        Item Number 4

       

      After Macro:

       

      Order 1     Supplier 1       Product          Item Number 1

      Order 1     Supplier 1       Add Cost        Item Number 2

      Order 1     Supplier 1       Add Cost        Item Number 3

      Order 2     Supplier 2        Product          Item Number 1

      Order 2     Supplier 2        Add Cost        Item Number 2

      Order 2     Supplier 3        Product          Item Number 3

      Order 2     Supplier 3        Add Cost        Item Number 4

       

      What I'm wondering is if there is any way to handle this kind of functionality in Prep. This is currently the only place where the automation process will fall down so I'd like to find a solution if possible. If not possible in Prep, can anyone recommend any other method to fix this that can run automatically?

       

      Rory

        • 1. Re: Using Prep as a Replacement for an Excel Macro
          Joshua Milligan

          Hi Rory,

           

          It would definitely be easy if Tableau Prep had a way to work across rows.  It does not currently, but you might consider voting for such a feature here: https://community.tableau.com/ideas/8732

           

          In the meantime, it still might be possible.  A lot would depend on if you had a way to identify the order of rows or orders.  In your example above, you have "Item Number 1", "2", "3", etc...  That would give you something to work with to implement a solution.  However, I'm not sure if your actual data follows that pattern.  Or perhaps there's a date/time field or Row ID field that could be leveraged.  Would you be able to share some details about the actual data set to see what we might be able to do?

           

          Best Regards,

          Joshua

          • 2. Re: Using Prep as a Replacement for an Excel Macro
            Rory Wallis

            Hi Joshua,

             

            Thanks for taking the time to reply. I managed to develop a solution that will be tested later today (hence I haven't marked this as closed yet). You are right in that there is something that can be done using the item number. The way an order is laid out is that the item numbers for additional costs will follow exactly after that for the product that they are for (as per my example). This means that the add costs can be matched to a product if their item number falls between the product codes for 2 consecutive products in a sales order (so add costs with item number 2 and 3 must fall between products with item numbers 1 and 4 and will be associated with product 1).

             

            All I did was isolate the products (i.e. removed the add costs), duplicate the data and joined it to itself. This gave me a combination of every item number with every other item number for a given order. I then filtered to keep only those from the duplicate data whose item number was greater than the original. I then did an aggregate to get the min item number from the duplicate. This then meant I had the range of add costs covered by each product. A simple join to the add costs data then gives me what I need. I'm hoping today's testing goes well and I will update if it does.

             

            Rory

            • 3. Re: Using Prep as a Replacement for an Excel Macro
              Joshua Milligan

              Rory,

               

              That's great!  It sounds very close to the approach I had in mind.  Let me know how testing goes!

               

              Best Regards,

              Joshua