Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Match Transform Usage

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.

Former Member
replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question