Data mapping thought Rules

[#1]

Hi all,

i have excel and database as a datasource.
in excel i have the data for different Business units for ex:ind,rus
below fields are in excel

ID,countrycode,BudgetI,CatID,PrgNM,CAtBudgetI,lcc,lcd,inncat,category etc..

i have 2 dimension tables
RT,RE

RT have following fields

RTtype,Dataelements

example

RTtype,    dataelements

1a ind 01, BudgetI,CatID
1b ind 02, CatID,prgNM,lcc
1c ind 03, lcd,lcc,inncat
1d ind 04,  lcd,lcc,Catid
...
2a rus 01,  Catid,lcd
2b rus 02,  lcd
2c rus 03, inncat,budgeti....

RE have following fields,

RTtype,BudgetI,CatID,PrgNM,CAtBudgetI,lcc,lcd,inncat,category

Problem ;

if i dont have value for category in excel i need to get value from RE table by
using RTtype and dataelements as a lookup and ineed to fetch value from RE by considering
priority of RTtype (example for ind:
first in need to consider 1a ind 01 ,i need to see if corresponding values of BudgetI,CatID
in both Excel and RE matched or not ,if matches then i need to pick corrsponding category from
RE,if it dd not match i need to go for second RTtype 1b ind 02, do the same for all business units)

Please Suggest a Solution

Thanks in Advance.

[Anonymous]

upload a sample qvw

[avinashelite · Accepted solution]

please share the sample app

[#4]

hi avinash here is the sample code

Type:
LOAD * INLINE [
    type, rule
    1a UZB 01, Lcc,Cc
    1b UZB 02, Cc 
    1c UZB 03, Lcc
];

entry:
LOAD * INLINE [
    type, Lcc, C,MGCode,r
    1a ind 01, a, 11,001,lcc,cc
    1b ind 02, , 23,001-01,cc
    1c ind 03, q,002,lcc,cc
    1a ind 01, b, 12,002-01,lcc,cc
];

map:
mapping
load r,mgcode resident entry;

exel:
LOAD ID,
     countrycode, 
     MGCODE
     lcc,
     LCD
     CC ,
     PrgNM ,
      BudgetI
     
   FROM
dump.xlsx
(ooxml, embedded labels, table is Sheet1) where isnull(MGCODE);

TempList:
load concat(chr(39) & type & chr(39),',') as KeyList
Resident Type;

let vKeyList = peek('KeyList');

for i=1 to NoOfRows(exel)

   for each vKey in $(vKeyList)

     let vFieldName =Lookup('rule','type','$(vKey)','KeyValList');

      Output:
        Load
        ID,
        countrycode,  
        lcc,
        LCD
        CC ,
        PrgNM ,
        BudgetI,
        applymap('map',$(vFieldName ),null()) as MGCODE //(mgcode is not null then should not
go for next of vkey ,it should go for next of i )
    RESIDENT exel
    where countrycode=subfield('$(vKey)','',2);  
  
next vkey

next i

here if mgcode have value for first vkey ,then loop should not repeat for
next vkey in vkeylist and should go for next line in exel.how i can achieve it

and i need use any other function instead of applymap to get the mgcode ?

Please suggest solution