SQL Query to QlikView Script Inner Join & IntervalMatch

rammarthi — Mon, 12 Feb 2018 21:43:08 GMT

I have two tables along with the SQL Query as below:

Table_A:

ID	Subject	DateOfBirth	Age
2	Math	11-09-1989	29
3	Math	24-09-1990	28
4	Math	30-08-1991	27
5	Science	30-07-2004	14
6	Science	31-08-2009	09
7	Science	24-09-1995	23

Table_B:

ID	Start_Date	End_Date	Grade
3	1-1-1990	31-12-1999	Youth
4	1-1-2000	31-12-2010	Teenagers
5	1-1-2011	31-12-2020	Kids

SELECT A1.ID, B1.Grade, sum(A1.Age) from Table_A A1

INNER JOIN Table_B B1 ON A1.ID = B1.ID AND A1.DateOfBirth BETWEEN B1.Start_Date AND B1.End_Date

where A1.DateOfBirth BETWEEN '1-1-1990' AND '31-12-2006'

AND A1.Subject = 'Math'

group by A1.ID, B.Grade

How to convert this Statement to QlikView Script? Can we use IntervalMatch?

Result:

A1.ID	B1.Grade	A1.Age
3	Youth	28
4	Youth	27

Re: SQL Query to QlikView Script Inner Join & IntervalMatch

sunny_talwar — Mon, 12 Feb 2018 23:57:19 GMT

May be this

TableA:

LOAD ID,

Subject,

DateOfBirth,

Age

FROM

[https://community.qlik.com/thread/291161]

(html, codepage is 1252, embedded labels, table is @1);

TableB:

LOAD ID,

Start_Date,

End_Date,

Grade

FROM

[https://community.qlik.com/thread/291161]

(html, codepage is 1252, embedded labels, table is @2);

Inner Join (TableA)

IntervalMatch(DateOfBirth, ID)

LOAD Start_Date,

End_Date,

Resident TableB;

Inner Join (TableA)

LOAD *

Resident TableB;

DROP Table TableB;