topic Re: Field value based on 2 joins in QlikView

Field value based on 2 joins

Thu, 05 Feb 2015 01:01:32 GMT

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.

Re: Field value based on 2 joins

Oleg_Troyansky — Thu, 05 Feb 2015 01:08:15 GMT

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

Re: Field value based on 2 joins

Thu, 05 Feb 2015 03:17:21 GMT

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

Re: Field value based on 2 joins

jagan — Thu, 05 Feb 2015 03:54:27 GMT

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.

Re: Field value based on 2 joins

Fri, 06 Feb 2015 22:49:05 GMT

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?

Re: Field value based on 2 joins

jagan — Mon, 09 Feb 2015 02:36:01 GMT

Hi,

Can you attach sample data and your expected output?

Regards,

Jagan.

Re: Field value based on 2 joins

Tue, 10 Feb 2015 03:44:57 GMT

I was able to get this working. Thank you!