Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
flottmen
Contributor
Contributor

Complex Join

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?

7 Replies
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

jonathandienst
Partner - Champion III
Partner - Champion III

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).

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
flottmen
Contributor
Contributor
Author

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.

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

flottmen
Contributor
Contributor
Author

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?

marcus_sommer

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

flottmen
Contributor
Contributor
Author

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.