Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

anavin33
New Contributor III

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_michaelid
Honored Contributor II

Re: Taking one response from a field

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

8 Replies
jason_michaelid
Honored Contributor II

Re: Taking one response from a field

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

Re: Taking one response from a field

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

Re: Taking one response from a field


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

anavin33
New Contributor III

Re: Taking one response from a field

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?

anavin33
New Contributor III

Re: Taking one response from a field

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

Re: Taking one response from a field

Can you paste the section of the load script?

So we can see in context?

anavin33
New Contributor III

Re: Taking one response from a field

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_michaelid
Honored Contributor II

Re: Taking one response from a field

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