Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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).
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.
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
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?
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
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.