Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Script: how to filter to load only if value is in one of several ranges

I have two data sources, one containing transactional data of cost centre and amount, and another parameter table listing the ranges of cost centres that I actually want to load.  Records where the cost centre is not in any of the ranges should be dropped.

Transactional Data:

Cost Centre, Amount

1001, 25.7

1002, 45.6

1003, 34.6

1004,45.1

1005, 1.2

1006, 33.9

1007, 33.4

1008, 1.8

1009, 88.4

1010, 5.4

Cost Center Ranges to be loaded:

Min, Max

1002, 1004

1007, 1009

In this simple example, my loaded table would be:

Cost Centre, Amount

1002, 45.6

1003, 34.6

1004,45.1

1007, 33.4

1008, 1.8

1009, 88.4

All I can come up with is to convert the ranges into individual values (e.g. 1002 to 1004 becomes 1002, 1003, 1004) and do a join or where exists.  Any suggestions of an easier way to accomplish this?   The list of ranges can change so there can be no hard-coding in the script.


Thanks,

Gareth.

1 Solution

Accepted Solutions
Highlighted

Try this

Table:

LOAD Min + IterNo() - 1 as [Cost Centre]

While Min + IterNo() - 1 <= Max;

LOAD * INLINE [

    Min, Max

    1002, 1004

    1007, 1009

];

Fact:

LOAD * Inline [

Cost Centre, Amount

1001, 25.7

1002, 45.6

1003, 34.6

1004,45.1

1005, 1.2

1006, 33.9

1007, 33.4

1008, 1.8

1009, 88.4

1010, 5.4

] Where Exists ([Cost Centre]);

DROP Table Table;

View solution in original post

2 Replies
Highlighted

Try this

Table:

LOAD Min + IterNo() - 1 as [Cost Centre]

While Min + IterNo() - 1 <= Max;

LOAD * INLINE [

    Min, Max

    1002, 1004

    1007, 1009

];

Fact:

LOAD * Inline [

Cost Centre, Amount

1001, 25.7

1002, 45.6

1003, 34.6

1004,45.1

1005, 1.2

1006, 33.9

1007, 33.4

1008, 1.8

1009, 88.4

1010, 5.4

] Where Exists ([Cost Centre]);

DROP Table Table;

View solution in original post

Highlighted
Contributor
Contributor

Works like a champ.  Thanks!