Announcements
cancel
Showing results for
Did you mean:
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
MVP

Try this

Table:

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

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

Min, Max

1002, 1004

1007, 1009

];

Fact:

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;

2 Replies
MVP

Try this

Table:

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

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

Min, Max

1002, 1004

1007, 1009

];

Fact:

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;

Contributor
Author

Works like a champ.  Thanks!

Community Browser