Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

rammarthi
Contributor

SQL Query to QlikView Script Inner Join & IntervalMatch

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

Table_A:

IDSubjectDateOfBirthAge
2Math11-09-198929
3Math24-09-199028
4Math30-08-199127
5Science30-07-200414
6Science31-08-200909
7Science24-09-199523

Table_B:

IDStart_DateEnd_DateGrade
31-1-199031-12-1999Youth
41-1-200031-12-2010Teenagers
51-1-201131-12-2020Kids

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.IDB1.GradeA1.Age
3Youth28
4Youth27
1 Solution

Accepted Solutions

Re: SQL Query to QlikView Script Inner Join & IntervalMatch

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,

ID

Resident TableB;


Inner Join (TableA)

LOAD *

Resident TableB;


DROP Table TableB;

View solution in original post

1 Reply

Re: SQL Query to QlikView Script Inner Join & IntervalMatch

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,

ID

Resident TableB;


Inner Join (TableA)

LOAD *

Resident TableB;


DROP Table TableB;

View solution in original post