topic Re: Complex Join in App Development

Complex Join

flottmen — Mon, 06 Mar 2017 23:05:25 GMT

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 — Tue, 07 Mar 2017 08:53:52 GMT

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

jonathandienst — Tue, 07 Mar 2017 09:46:17 GMT

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

flottmen — Tue, 07 Mar 2017 17:36:29 GMT

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 — Tue, 07 Mar 2017 18:06:14 GMT

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

flottmen — Tue, 07 Mar 2017 22:30:49 GMT

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

marcus_sommer — Wed, 08 Mar 2017 08:03:21 GMT

There is no sorting within your mapping-load - add just the following:

[Map_Overrides]:

MAPPING LOAD

Id, Amount

RESIDENT [Overrides] order by Date desc;

- Marcus

Re: Complex Join

flottmen — Tue, 18 Apr 2017 22:47:02 GMT

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.