KNOWLEDGE BASE

Convert LOOKUP Functions In Tableau Desktop Workbook By Tableau Prep Builder


Published: 04 Jun 2020
Last Modified Date: 10 Jun 2020

Question

How to convert the LOOKUP function used in Tableau Desktop by Tableau Prep Builder.

Environment

  • Tableau Prep Builder

Answer

In the attached packaged workbook, the LOOKUP function is originally used to get the previous order date per customer with Sample - Superstore. The following steps and the attached packaged flow file demonstrates how to get the same result using Tableau Prep Builder.
  1. Open the Orders sheet of Sample - Superstore by Tableau Prep Builder
  2. Add Clean Step.
  3. In the Clean 1 step, click ... (More Options) of Order Date > Create Calculated Field > Rank.
  4. Set Rank as below.
    • Select Rank at the first drop down list.
    • Group by Customer Name
    • Order by Defense Rank
    • Descending order
      User-added image
  5. Select Orders and Clean 1 step with pressing ctrl key, then right-click > Copy.
  6. Right-click blank space > Paste. Orders(1) and Clean 1(1) step are generated.
  7. In the Clean 1(1) step, click Create Calculated Field and create a calculated field (Index For Order Date)+1 including the following formula:
    [Index For Order Date]+1
  8. Delete all other field except (Index For Order Date)+1, Customer Name and Order Date.
  9. Left join the Clean 1 step to Clean 1(1) step at the Join 1 step with the following join clause.
    • Index For Order Date = (Index For Order Date)+1
    • Customer Name = Customer Name.
  10. Add Clean Step.
  11. In the Clean 3 step, rename Order Date-1 to Previous Order Date and remove Index For Order Date and (Index For Order Date)+1
  12. Add Output.

Additional Information

Table calculation functions in Tableau Desktop, such as the LOOKUP functions, sometimes get unexpected results depends on the layout or level of details of views. To avoid the issue, converting table calculations by Tableau Prep Builder would be a solution.
Did this article resolve the issue?