on 06-05-2015 6:56 AM
Hi,
For the below scenario, can someone suggest on how this can be achieved using Match Transform and which option to select when we drag Match Transform in the structure.
Remember that if there is a non-null value in the benchmark that is not equal to the equivalent criteria in the Hosting input then there is no match. Therefore those with region = IN would not match any of the given benchmarks. It is best fit as long as the non-matching values are to null value benchmark criteria.
Hosting_Site (Source File)
YEAR MONTH HOSTING CLASS REGION HOSTING COST
2015 1 A UK 75
2015 1 A IN 80
2015 1 B IN 60
2015 1 C US 100
BENCHMARK (Lookup Table)
Row Year Month Hosting class Region BENCHMARK COST
1 2015 1 A US 100
2 2015 1 B US 200
3 2015 1 A UK 50
4 2015 1 A null 70
STANDARD CLASS COST (If the criteria between source file and the lookup file doesn't match then have to load this cost)
Row Year Hosting class BENCHMARK COST
1 2015 A 22
2 2015 B 33
Then result should be:
FACT LOAD:
YEAR MONTH HOSTING CLASS REGION HOSTING COST BENCHMARK COST
2015 1 A UK 75 50
2015 1 A IN 80 70
2015 1 B IN 60 33 (Since for B class there is no region = IN or = null the Standard Class Cost must be used)
2015 1 C US 100 ??? (Reject record since there is no default value in Standard Class Cost or in Benchmark)
Thanks,
Abdulrasheed.
No need for a match transform, query transforms with decode and lookup built-in functions will do.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Define a mapping for "BENCHMARK COST" in 3 consecutive Query transforms.
Query transform #1: lookup(BENCHMARK, "BENCHMARK COST", null, 'PRE_LOAD_CACHE', YEAR, YEAR, MONTH, MONTH, "HOSTING CLASS ", "HOSTING CLASS ", REGION, REGION)
Query transform #2: decode("BENCHMARK COST" is not null, "BENCHMARK COST", lookup(BENCHMARK, "BENCHMARK COST", null, 'PRE_LOAD_CACHE', YEAR, YEAR, MONTH, MONTH, "HOSTING CLASS ", "HOSTING CLASS ", REGION, null))
Query transform #3: decode("BENCHMARK COST" is not null, "BENCHMARK COST", lookup("STANDARD CLASS COST", "BENCHMARK COST", null, 'PRE_LOAD_CACHE', YEAR, YEAR, "HOSTING CLASS ", "HOSTING CLASS "))
Hi Dirk,
Even if we are able to achieve what had asked earlier, along with REGION if any other column from BENCHMARK is having NULLs then in that case, this logic might not work. Correct me on this.
As the way what we have done for REGION in the query2, it might be bit hectic to do it for rest of columns by adding few more query transforms with decode & lookup functions.
Will there be a way for this to handle the scenario where there are different combination of data present in Benchmark table. Hosting sites, we will not get NULLs as the job we have will handle that NULL scenario.
Thanks,
Abdulrasheed
Obviously, the more complex your business rules, the more complex your solution logic. i am afraid there's no free lunch.
Note that you don't need multiple query transforms for the logic, I had just solved it that way for reasons of simplicity. The following mapping in a single query transform is just as valid:
decode (
lookup(BENCHMARK, "BENCHMARK COST", null, 'PRE_LOAD_CACHE', YEAR, YEAR, MONTH, MONTH, "HOSTING CLASS ", "HOSTING CLASS ", REGION, REGION) is not null,
lookup(BENCHMARK, "BENCHMARK COST", null, 'PRE_LOAD_CACHE', YEAR, YEAR, MONTH, MONTH, "HOSTING CLASS ", "HOSTING CLASS ", REGION, REGION),
lookup(BENCHMARK, "BENCHMARK COST", null, 'PRE_LOAD_CACHE', YEAR, YEAR, MONTH, MONTH, "HOSTING CLASS ", "HOSTING CLASS ", REGION, null) is not null,
lookup(BENCHMARK, "BENCHMARK COST", null, 'PRE_LOAD_CACHE', YEAR, YEAR, MONTH, MONTH, "HOSTING CLASS ", "HOSTING CLASS ", REGION, null),
lookup("STANDARD CLASS COST", "BENCHMARK COST", null, 'PRE_LOAD_CACHE', YEAR, YEAR, "HOSTING CLASS ", "HOSTING CLASS ") is not null
lookup("STANDARD CLASS COST", "BENCHMARK COST", null, 'PRE_LOAD_CACHE', YEAR, YEAR, "HOSTING CLASS ", "HOSTING CLASS "),
null)
Hi Dirk,
If it's with real time data then there might be a chance where in benchmark, few columns data might get populated with NULLs and for that many more lookups should get added into the logic. There are permutation & combination of data for this each column and for this the logic will be so huge. Not sure if it works in the above scenario.
Thanks,
Abdulrasheed.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.