// combine UDF Fields and UDF Values

UDF_1:
LOAD [RecID] as UDFFieldRecID,[Name] as UDFName;
SQL select `RecID`,
`Name`
from `UDF Fields`;
Inner Join
UDF_Value:
Load ParentRecID as UDFFieldRecID,
OwnerRecID as LoanRecID,
Value as UDFValue;
SQL select `ParentRecID`,
`OwnerRecID` ,
`Value`
from `UDF Values`;

// ————————————————————–
// separte non-commitment records
Commitment_1:
NoConcatenate load
trim(subfield(UDFName,’ ‘,2)) as ConnectLender,
num(UDFValue) as CommitAmt,
LoanRecID
Resident UDF_1 where wildmatch(UDFName,’COMM*’);
IRR_1:
NoConcatenate load
trim(subfield(UDFName,’ ‘,2)) as ConnectLender,
num(UDFValue) as IRRAmt,
LoanRecID
Resident UDF_1 where wildmatch(UDFName,’IRR*’);

RR_1:
NoConcatenate load
trim(subfield(UDFName,’ ‘,3)) as ConnectLender,
num(UDFValue) as Risk,
LoanRecID
Resident UDF_1 where wildmatch(UDFName,’Risk*’);
UDF:
NoConcatenate Load LoanRecID,
// UDFName,
subfield(UDFValue,’ ‘,1) as MaturityDateOrig
Resident UDF_1 where wildmatch(UDFName,’Mat*’);

Maturity:
NoConcatenate Load LoanRecID,
Date(MaturityDateOrig,’MM,DD,YYYY’) as MaturityDateOrig
resident UDF;
drop table UDF;
AcctOrig:
NoConcatenate Load LoanRecID,
// UDFName,
UDFValue as AccountOrig
Resident UDF_1 where wildmatch(UDFName,’Account*’);

drop table UDF_1;

// now logic to add funding/commitment information to funding record
// Map_Lender:
// mapping Load
// trim([Account.Lender]) as ConnectLender,
// LenderRecID
// resident Lenders;
// this now is created in Loans module since it is used of LTV in Property module

Commitment:
NoConcatenate LOAD LoanRecID
, ConnectLender
, CommitAmt
,ApplyMap(‘Map_Lender’,ConnectLender, ‘Committ Map’) as LenderRecID
resident Commitment_1;
drop table Commitment_1;

IRR:
NoConcatenate LOAD LoanRecID
, ConnectLender
, IRRAmt
,ApplyMap(‘Map_Lender’,ConnectLender, ‘Mapping not found’) as LenderRecID
resident IRR_1;
drop table IRR_1;

RR:
NoConcatenate LOAD LoanRecID
, ConnectLender
, Risk
,ApplyMap(‘Map_Lender’,ConnectLender, ‘Mapping not found’) as LenderRecID
resident RR_1;
drop table RR_1;
Map_key_c:
mapping Load
autonumber(LoanRecID&LenderRecID) as mapkey,
CommitAmt
resident Commitment;

Map_key_i:
mapping Load
autonumber(LoanRecID&LenderRecID) as mapkey,
IRRAmt
resident IRR;

Map_key_r:
mapping Load
autonumber(LoanRecID&LenderRecID) as mapkey,
Risk
resident RR;

Map_key_l:
mapping Load
autonumber(LoanRecID&LenderRecID) as mapkey,
LTV
resident LTV;

Funding_1:
LOAD BrokerFeePct,
DrawControl,
EffRateValue,
FundControl,
LenderRecID,
LoanRecID,
if(FundControl=0,’Fund-0′,’Funding’) as FactType,
autonumber(LoanRecID&LenderRecID) as mapkey;
SQL SELECT `BrokerFeePct`,
`DrawControl`,
`EffRateValue`,
`FundControl`,
`LenderRecID`,
`LoanRecID`
FROM `TDS Funding`;
// where FundControl > 0;

// split 0 and non 0 to get rid of funding = 0, doesn’t seem to work though
// Funding_old:
// NoConcatenate load *
// resident Funding_1 where FactType = ‘Fund-0’;

// this should only have non zero funding records from funding table
Funding_cur:
NoConcatenate load *
resident Funding_1 where FactType = ‘Funding’;

// drop table Funding_old;
drop table Funding_1;

// using the mapping function instead of join

Funding:
load *,
ApplyMap(‘Map_key_c’,mapkey,’no Comm match’) as CommitAmt,
ApplyMap(‘Map_key_l’,mapkey,’no LTV match’) as LTV,
ApplyMap(‘Map_key_i’,mapkey,’no IRR match’ ) as IRRAmt
// ApplyMap(‘Map_key_r’,mapkey,’no RR match’) as Risk
resident Funding_cur;

// this is for testing only, later just do the drop
// drop field LenderRecID from Commitment;
// Commitment_t:
// load LoanRecID,
// ConnectLender,
// CommitAmt as UDFCommited
// resident Commitment;

drop table Commitment;
drop table Funding_cur;
drop table IRR;
drop table RR;
drop table LTV;