cancel
Showing results for 
Search instead for 
Did you mean: 

Match Transform Usage

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

former_member187605
Active Contributor
0 Kudos

No need for a match transform, query transforms with decode and lookup built-in functions will do.

Former Member
0 Kudos

Hi Drik,

Thanks for this. Is it like decode within decode where in the source column value should match against the lookup column value. Is my understanding correct. Please share if you have any thoughts about the logic.

Regards,

Abdulrasheed.

former_member187605
Active Contributor
0 Kudos

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 "))

Former Member
0 Kudos

Thanks a Lot Dirk. It's worked.

Former Member
0 Kudos

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

former_member187605
Active Contributor
0 Kudos

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)

Former Member
0 Kudos

Thanks Again Dirk. It's working. Will apply this with real scenario of data and will see how it works.

Regards,

Abdulrasheed.

Former Member
0 Kudos

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.

former_member187605
Active Contributor
0 Kudos

The more complex your business rules are, the more complex your logic will be. I am afraid there won't be any way around.

Answers (0)