Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
it looks like you need to use an extended syntax of intervalmatch with the field code as an extra key.
I'll have to read up on this. Thanks for the help.
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.
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?
Hi,
Can you attach sample data and your expected output?
Regards,
Jagan.
I was able to get this working. Thank you!