Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Join two tables with no common key based on condtions

I have two tables;

ScoreTable that includs name and score.

And GradeTable that includes criterias (score limits) for different grades.

The solution I have come upp with is by first outer joing the tables and then evaluates the rows. This solution below works fine. But  when joining this way with Cartesian prodcuts the Scoretable will include all combinations from the ScoreTable and the GradeTable. If I would have 100 rows in each table it would result in 10000 rows. Increasing rows more would Quick be difficult for the RAM to handle this approach. So my question is how else could I solve this problem below?

ScoreTable:
Load * Inline[
Name, Score
Sarah, 45
John, 32
Bob, 8
Will, 14
Anna, 22
Henry, 17
Marcus, 65
]
;


GradeTable:
LOAD * Inline [
Grade, Low_limit, High_limit
A, 60, 70
B, 50, 59
C, 40, 49
D, 0, 39
]
;

Outer Join (ScoreTable)
Load
Grade,
Low_limit,
High_limit
Resident GradeTable;

drop table GradeTable;

ResultTable:
Load
Name,
Grade
Resident ScoreTable
Where Score >= Low_limit and Score <= High_limit;

Drop table  ScoreTable;

Tags (2)
1 Reply
Highlighted
Contributor
Contributor

Re: Join two tables with no common key based on condtions

Find Another solution for these specifik tables by using join intervalMatch

See solution below. Is there a more generic solution where it could be more criterias specified using if-conditions?

ScoreTable:
Load * Inline[
Name, Score
Sarah, 45
John, 32
Bob, 8
Will, 14
Anna, 22
Henry, 17
Marcus, 65
]
;



GradeTable:
LOAD * Inline [
Grade, Low_limit, High_limit
A, 60, 70
B, 50, 59
C, 40, 49
D, 0, 39
]
;


right Join(GradeTable) IntervalMatch (Score)
LOAD
Low_limit,
High_limit
Resident GradeTable;


Left join(ScoreTable)
Load
Score,
Grade
Resident GradeTable;

Drop table GradeTable;