Field value based on 2 joins

[#1]

Table 1 contains
IssueDate
Code

Table 2 contains
Code
Country
DateCreated
DateExpired

What I need to do is get the matching country added as an additional
field in Table 1.  Between the 2 tables, Code field must match  and (Table1.IssueDate >= Table2.DateCreated and Table1.IssueDate <= DateExpired)

Example of Table1 contents
IssueDate / Code
2014-01-01 / BB
2013-01-06 / BB
2014-05-01 / CC
2014-06-01 / CC

Example of Table2 contents
Code / Country / DateCreated / DateExpired
BB / US / 2013-01-05 / 2013-01-06
BB / US / 2013-01-08 / 2014-01-01
BB / DE / 2014-01-02 / 2015-06-01
CC / US / 2014-01-01 / 2014-12-31
CC / US / 2015-01-01 / 2015-12-31

Given the example data, I would want the following

IssueDate / Code / Country
2014-01-01 / BB / ------ (no match)
2013-01-06 / BB / US
2014-06-01 / CC / US
2014-06-01 / CC / DE

Normally, I would prep the data in SQL but the tables are coming from 2 different datasources.

Suggestions on how I might achieve this in QV?  Thanks for some direction.

[Oleg_Troyansky]

it looks like you need to use an extended syntax of intervalmatch with the field code as an extra key.

[#3]

I'll have to read up on this.  Thanks for the help.

[jagan · Accepted solution]

HI,

Check this sample script for IntervalMatch Extended syntax

Data:

LOAD * INLINE

[ ID, Name, Dept

1, James, HR

2, John, PR ];

Match:

LOAD * INLINE

[ Dept, Start, End, Range

HR, 1, 5, 1-5

PR, 2, 10, 2-10];

INNER JOIN

IntervalMatch(ID, Dept)

LOAD Distinct Start, End, Dept

Resident Match;

Regards,

Jagan.

[#5]

I don't work with QV much so likely I'm missing something.  I've played around with this and tried to apply to my scenario but don't see how it's going to work given I need to match 2 fields from my "Data" table to my "Match" table.  For example in my "Data" Table if I have

Code     DateIssue

AA          2014-01-02

AA          2015-06-01

"Match" Table

Code     DateCreate     DateExpired     Country

AA          2013-01-01     2014-01-01     US

AA          2014-01-01                             GB

I need to be able to "join" the codes and then check if the DataTable.DateIssue is between MatchTable.DateCreate and MatchTable.DateExpired.  If it is return the Country.  What am I missing?

[jagan]

Hi,

Can you attach sample data and your expected output?

Regards,

Jagan.

[#7]

I was able to get this working.  Thank you!