Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;