8 Replies Latest reply on Nov 27, 2012 10:50 AM by Steve Demanovich

    Lookup Function

    Steve Demanovich

      I have a dataset that has among other dimensions, contract id and original contract id.  Each row has a contract id but may or may not have an original contract id.  Contract ids are unique, but can become original contract ids for different contract ids.  So, the same contract id may exist on another row as an original contract id, but only once.  What I want to do is look at a contract id, and determine if that contract id exists as an original contract id on any other row in the data, then use that as a filter.  Example:

      Contract ID    Original Contract ID

      122333            110011       
      110011
      195544164222
      188550
      164222128999

       

      In this example, I would want contract ids 110011 and 164222 to be marked as '0' because they also appear elsewhere as an original contract id.  Contract ids 122333, 195544, and 188550 would be marked as '1' because they do not appear as an original contract id in the data.

       

      Is this possible, and if so what is the best way to accomplish this?

       

      Thanks!

        • 1. Re: Lookup Function
          Alex Kerin

          The attached can do this, but I don't know how scalable it is (a text field is built up that contains every Original ID)

           

          Another possible way is through custom SQL and a join. I'm not good enough at SQL to do that though.

           

          Yet another possibility is to reshape the data to one column of IDs (also attached). You could do this at the source or in Custom SQL. Then you can check the number of appearances of an ID. You can even combine the 2 sheets to have a (hopefully) scalable lookup with the data in its original format.

           

          If this is coming from Excel, you could also use a vlookup  to achieve this quickly.

          1 of 1 people found this helpful
          • 2. Re: Lookup Function
            Steve Demanovich

            This might work, I'll give it a try.  I have about 50K rows of data but no ID would appear more than twice.  It's coming from SQL so my hope was to avoid Excel...the vlookup was easy to do but I didn't want the extra step since this will be done every month.  Thanks for the tip, I'll see how it goes!

            • 3. Re: Lookup Function
              Eric McDonald

              If it's coming from SQL why not do it in SQL? This keeps the data logic all in one place.

              • 4. Re: Lookup Function
                Steve Demanovich

                I would love to but I'm not SQL savvy enough to write that logic.  That would be my first choice.

                • 5. Re: Lookup Function
                  Jonathan Drummey

                  Here's some SQL that can do the job:

                   

                  SELECT [Sheet1$].[Contract ID] AS [Contract ID],

                    [Sheet1$].[Original Contract ID] AS [Original Contract ID],

                    IIF(ISNULL([2].[Original Contract ID]),1,0) AS [Found Original Contract ID]

                  FROM [Sheet1$]

                  LEFT JOIN (SELECT DISTINCT [Sheet1$].[Original Contract ID] FROM [Sheet1$]) AS [2]

                    ON [Sheet1$].[Contract ID] = [2].[Original Contract ID]

                   

                  The key bit here is that the left join is based on the distinct list of Original Contract ID's (so that way if there are N records with the same Original Contract ID we won't be multiplying rows), and then the Found Original Contract ID is simply a test on whether a value was found or not in the left join.

                   

                  Jonathan

                  • 6. Re: Lookup Function
                    Steve Demanovich

                    Jonathan - that does exactly what I'd like it to. Now, forgive my ignorance but can that same logic be embedded in the SQL I have attached, which is the code I use to pull the entire dataset?  Or do I just need a second data source in addition to my main one? 

                     

                    Thanks!

                    Steve

                    • 7. Re: Lookup Function
                      Jonathan Drummey

                      I always try to avoid creating additional data sources until I absolutely have to, you should be able to add this to your SQL.

                      Looking at the query, it seems like you'd need to embed the logic twice, once for each SELECT statement within the UNION.

                      1 of 1 people found this helpful
                      • 8. Re: Lookup Function
                        Steve Demanovich

                        This will be a learning experience!  I might have to call on my SQL experts to help out, but once I can embed it correctly I think this will do the trick.  Thanks again!