7 Replies Latest reply: Apr 18, 2017 6:47 PM by Eric Flottmann RSS

    Complex Join

    Eric Flottmann

      I have a table that is full of transactions.  These transactions have a date entered, a campaign id and an affiliate id (among other things).  I need to join this transaction table to a table of overrides.  These overrides join on a combination of campaign id and affiliate id (easy so far).  However, multiple overrides can be created over time.  For this join, I need to join only the most recent override that was added prior to the transaction being created.

       

      Here is my script so far:

       

      LEFT JOIN ([table])

      LOAD

        AffiliateId,

        CampaignId,

          FirstSortedValue(override_value, -Override_DateEffective) AS Override_Value   

      RESIDENT [override_table]

      WHERE Override_DateEffective < Transaction_DateEntered

       

      Seems to me that the Where clause should only allow the join to accept overrides that existed prior to the transaction.  Then the FirstSortedValue function selects the most recent of these values (the one I want).  However, the Transaction_DateEntered is not visible to query.  Can I use a PEEK?  If so, how do I determine the row number for that function?

        • Re: Complex Join
          Marcus Sommer

          I believe you couldn't do it with a single join-statement else you would need further steps to limit this override-load or to filter unwanted records after the join. Therefore I think a mapping approach with a composite key of campaign id + affiliate id and your override value will be more suitable. An applymap() will catch the value from the first matching like a lookup in excel and if your mapping-table is properly ordered to the most recent transaction date it should work.

           

          - Marcus

            • Re: Complex Join
              Eric Flottmann

              I simplified the example I gave.  I actually need to join an entire row (four columns) of data from the override table.  The Apply Map logic seems to restrict my map to one field only.

                • Re: Complex Join
                  Marcus Sommer

                  That's not a problem because you could concat your fields like: F1 & '|' & F2 & '|' & F3 & '|' & F4 and then you could use subfield(applymap('MapName', YourKey, '#NV'), '|', 1) as F1 to get your values splitted again. That's not uncommon and also quite fast.

                   

                  - Marcus

              • Re: Complex Join
                Jonathan Dienst

                Do you need to load all the overrides? It sounds to me like you should load only the most recent overrides, in which case the join is a simpler (and quicker).

                • Re: Complex Join
                  Eric Flottmann

                  Here is a simplified example that highlights my problem:

                   

                  [Overrides]:

                  LOAD * INLINE [

                    Id, Date, Amount

                      1, '1-1-16', 20

                      1, 2-1-16', 18

                  ];

                   

                   

                  [Transactions]:

                  LOAD * INLINE [

                    Id, Date

                      1, '1-3-16',

                      1, '1-5-16',

                      1, '1-30-16',

                      1, '2-2-16',

                      1, '2-20-16'

                  ];

                   

                   

                  [Map_Overrides]:

                  MAPPING LOAD

                    Id, Amount

                  RESIDENT [Overrides];

                   

                   

                  [Transaction_Override]:

                  LOAD Id,

                    Date(Date#(Date, 'MM-DD-YY'), 'MM/DD/YYYY') AS Date,

                    ApplyMap('Map_Overrides', Id) AS Override

                  RESIDENT [Transactions];

                     

                  DROP TABLE [Transactions];

                  DROP TABLE [Overrides];

                   

                  Even using Mapping all of the transactions are getting the $20 override.  I need the two transactions in February to map to the $18 override that was made effective on 2-1-16.  What logic can I add to this to make it work?

                  • Re: Complex Join
                    Eric Flottmann

                    I ended up using Interval Match to solve this issue.  That allowed me to select the appropriate based on the date of the record and the date range attached to the override.  If anyone needs help applying this to their model, let me know.