4 Replies Latest reply on May 1, 2013 9:06 AM by R E

    What happens when there is no exact match using LOOKUP?

    R E

      So i am getting unexpected results when trying to lookup a prior year's value based on same month-day of this year when the prior year does not have that month-day in the data. (working with store data where the stores are not open every day)

       

      Ideally, when this happens, i just want missing/null returned (equivalent to what happens when using exact match in excel v or h lookup).

       

      everything is working as expected except for those days that have no month-date match during the prior year.

       

      It appears what's happening is that tableau is trying to pull a value from the next closest match based on my table calc "compute using" fields, which are in this case, day(date), month(date), quarter(date), year(date) (at the level of year(date).

       

      any work around suggestions is much appreciated.

        • 1. Re: What happens when there is no exact match using LOOKUP?
          Shawn Wallwork

          The documentation says it's suppose to return Null:

          Lookup.png

          I just tested this: IF FIRST() = 0 THEN LOOKUP(SUM([Profit]),-1) END and it did indeed return nulls.

           

          Try recreating the same issue using the Super Store data set and then post it so we can see what's going on.

           

          --Shawn

          • 2. Re: What happens when there is no exact match using LOOKUP?
            Alex Kerin

            Yeah, there's no "closest match" going on here. If I tell Tableau to go back 12 rows and you only have 11 rows of monthly data for the last year, you're going to get a row from one year, one month ago.

             

            Now that may change with missing values/date densification - I'm not an expert on that by any stretch.

            • 3. Re: What happens when there is no exact match using LOOKUP?
              Jonathan Drummey

              Show Missing Values aka domain padding can pad dates so that the lookup would return a Null value, however you'd need to be careful about how the pills are laid out because the domain padding only occurs between the minimum and maximum values in the partition. So if a store had, say, sales only on the 2nd through the 27th then only the dates in-between would be padded. There are various ways to get around this limitation of domain padding by increasing the data within the view to get the padding to work and then using table calcs to filter it out, by adding some additional rows to the data, or building a scaffold source.

               

              Jonathan

              • 4. Re: What happens when there is no exact match using LOOKUP?
                R E

                Thanks all. If I understand correctly, there is nothing that looks up backwards  in a table for an exact match based on value. My workaround was to create a field that looks up what prior date tableau is pulling my target field from. Then comparing that date to the current date less 1 year. Only perform the lookup in my target field if these 2 dates match else return null. Works. Due to the number of stores and grouping fields it isn't practical for me to create dummy rows for missing dates because that would explode the data larger than necessary.