Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Taking one response from a field

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;

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

View solution in original post

8 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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

Not applicable
Author


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

Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
Author

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?

Not applicable
Author

Can you paste the section of the load script?

So we can see in context?

Anonymous
Not applicable
Author

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;

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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