Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Extended IntervalMatch

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

madhumitha

Creator

2017-10-17
03:19 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Extended IntervalMatch

Hello all,

Below is my script for extended interval match and the expected output. its simple not working and I can't figure out why. Could you please help?

Range:

**LOAD** * INLINE [

Max range, Min Range, Grade,Subj

1, 20, D,Math

21, 40, C,Math

41, 80, B,Math

81, 100, A,Math

1, 40, D,Science

41, 60, C,Science

61, 90, B,Science

91, 100, A,Science

];

Marks:

**Load** * Inline [

Stud_Nam,Marks, Subj

S1,31,Math

S2,45,Math

S3,62,Math

S4,82,Math

S5,90,Math

S1,21,Science

S2,35,Science

S3,52,Science

S4,72,Science

S5,83,Science

];

Inner join

IntervalMatch(Marks,Subj) **load** [Max range] as MaxRange, [Min Range] as MinRange resident Range;

expected output:

Stud_Nam | Subj | Marks | Grade |

S1 | Math | 31 | C |

S2 | Math | 45 | B |

S3 | Math | 62 | B |

S4 | Math | 82 | A |

S5 | Math | 90 | A |

S1 | Science | 21 | D |

S2 | Science | 35 | D |

S3 | Science | 52 | C |

S4 | Science | 72 | B |

S5 | Science | 83 | B |

2,992 Views

1 Solution

Accepted Solutions

swuehl

MVP

2017-10-17
03:42 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Range:

LOAD * INLINE [

Max range, Min Range, Grade,Subj

1, 20, D,Math

21, 40, C,Math

41, 80, B,Math

81, 100, A,Math

1, 40, D,Science

41, 60, C,Science

61, 90, B,Science

91, 100, A,Science

];

Marks:

Load * Inline [

Stud_Nam,Marks, Subj

S1,31,Math

S2,45,Math

S3,62,Math

S4,82,Math

S5,90,Math

S1,21,Science

S2,35,Science

S3,52,Science

S4,72,Science

S5,83,Science

];

**Inner join **

**IntervalMatch(Marks,Subj) load [Max range], [Min Range], Subj resident Range;**

**LEFT JOIN **

**LOAD * RESIDENT Range;**

**DROP TABLE Range;**

2,631 Views

6 Replies

oknotsen

Master III

2017-10-17
03:35 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You are forgetting to load "Subj" in your last load.

It should be:

Inner join

IntervalMatch(Marks,Subj) **load** [Max range] as MaxRange, [Min Range] as MinRange, Subj resident Range;

I would also add this:

drop Table Range;

May you live in interesting times!

2,631 Views

swuehl

MVP

2017-10-17
03:42 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Range:

LOAD * INLINE [

Max range, Min Range, Grade,Subj

1, 20, D,Math

21, 40, C,Math

41, 80, B,Math

81, 100, A,Math

1, 40, D,Science

41, 60, C,Science

61, 90, B,Science

91, 100, A,Science

];

Marks:

Load * Inline [

Stud_Nam,Marks, Subj

S1,31,Math

S2,45,Math

S3,62,Math

S4,82,Math

S5,90,Math

S1,21,Science

S2,35,Science

S3,52,Science

S4,72,Science

S5,83,Science

];

**Inner join **

**IntervalMatch(Marks,Subj) load [Max range], [Min Range], Subj resident Range;**

**LEFT JOIN **

**LOAD * RESIDENT Range;**

**DROP TABLE Range;**

2,632 Views

antoniotiman

Master III

2017-10-17
03:46 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May be this

Range:

**LOAD** *,[Max range]+IterNo()-1 as Marks INLINE [

Max range, Min Range, Grade,Subj

1, 20, D,Math

21, 40, C,Math

41, 80, B,Math

81, 100, A,Math

1, 40, D,Science

41, 60, C,Science

61, 90, B,Science

91, 100, A,Science

]While [Max range]+IterNo() <= [Min Range];

Marks:

Inner Join

**Load** * Inline [

Stud_Nam,Marks, Subj

S1,31,Math

S2,45,Math

S3,62,Math

S4,82,Math

S5,90,Math

S1,21,Science

S2,35,Science

S3,52,Science

S4,72,Science

S5,83,Science

];

madhumitha

Creator

2017-10-17
05:39 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Antonio. This exactly fetches me the desired result.

Is this scenario shouldn't be done using interval match at all?

2,631 Views

antoniotiman

Master III

2017-10-17
06:21 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I prefer this way.

Intervalmatch creates table Bridge between Your Tables.

IterNo() generates Rows in Table.

However Stefan's answer is correct way to use Intervalmatch.

2,631 Views

oknotsen

Master III

2017-10-18
06:07 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Downside of this method is that you can get a huge table in memory before your join where the table you created for the interval match stays tiny.

Your current example is only with a limited number of values, but imagine the change in size of the table between the two methods when you get a lot more values to match.

So to save memory during the reload and likely higher performance, I would still stick to the interval match.

If the amount of matches stays tiny and performance is far from an issues, it doesn't matter of course.

May you live in interesting times!

2,631 Views

Community Browser