Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So I'm using this table to get the type of acct each account is, but some of them are listed as two different types because they changed at some point. How do I narrow this down so that I can just get the most recent type instead of both
tmp:
Left Join (Loss)
Load
acct_nbr as distinctAcct,
[Type] as LoanTy
Resident Loans;
Maybe use a mapping function:
Map_LoanType:
MAPPING LOAD
acct_nbr
,Type
RESIDENT Loans
ORDER BY <SomeDateField> DESC;
Then, while loading the "Loss" table use
ApplyMap('Map_LoanType',acct_nbr,'default') AS LoanType
ApplyMap will take the first match it finds which will be the latest one as the mapping table was sorted by a date field descending.
Hope this helps,
Jason
Maybe use a mapping function:
Map_LoanType:
MAPPING LOAD
acct_nbr
,Type
RESIDENT Loans
ORDER BY <SomeDateField> DESC;
Then, while loading the "Loss" table use
ApplyMap('Map_LoanType',acct_nbr,'default') AS LoanType
ApplyMap will take the first match it finds which will be the latest one as the mapping table was sorted by a date field descending.
Hope this helps,
Jason
Ashley,
you may use FirstSortedValue().
Try this simple script:
t:
LOAD * INLINE [
F1, F2, F3
A, 1, T1
A, 2, T2
B, 1, T3
B, 2, T4
];
t1:
load FirstSortedValue(F3,F2) as type Resident t Group by F1;
drop table t;
regards
Darek
What about combining both suggestions
Map_LoanType:
MAPPING LOAD
acct_nbr
,firstsortedvalue(Type,-1) group by acct_nbr // the -1 on sortvalue takes last value
RESIDENT Loans
then
ApplyMap('Map_LoanType',acct_nbr,'default') AS LoanType
as suggested?
Richard
This seems to be returning the correct amount of lines but when I put the Loan Type variable created by the 'ApplyMap('Map_LoanType',acct_nbr,'default') AS LoanTy' line into a table to be displayed nothing is shown. Any suggestions?
This seems to be returning the correct amount of lines but when I put the Loan Type variable created by the 'ApplyMap('Map_LoanType',acct_nbr,'default') AS LoanTy' line into a table to be displayed nothing is shown. Any suggestions?
Can you paste the section of the load script?
So we can see in context?
Map_LoanType:
MAPPING
LOAD
acct_nbr as distinctAcct,
firstsortedvalue([Loan Type],-1) // the -1 on sortvalue takes last value
RESIDENT Loans
group by acct_nbr ;
LoanLoss:
Load
acct_nbr as distinctAcct,
date(min(prod_dt)) as [Date First 90+],
ApplyMap('Map_LoanType',acct_nbr,'default') AS LoanTy
Resident Loans
where is90 = 1
group by acct_nbr;
Remember that the third parameter for ApplyMap() is what will be returned in the absence of a match. I have used 'default', but you may prefer something else.
Glad you got it sorted.
Jason