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