topic Re: Taking one response from a field in QlikView

Taking one response from a field

Anonymous — Mon, 02 Jun 2014 13:12:29 GMT

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;

Re: Taking one response from a field

Jason_Michaelides — Mon, 02 Jun 2014 13:20:45 GMT

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

Re: Taking one response from a field

Mon, 02 Jun 2014 13:24:28 GMT

Ashley,

you may use FirstSortedValue().

Try this simple script:

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

Re: Taking one response from a field

Mon, 02 Jun 2014 13:34:49 GMT

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

Re: Taking one response from a field

Anonymous — Mon, 02 Jun 2014 14:03:35 GMT

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?

Re: Taking one response from a field

Anonymous — Mon, 02 Jun 2014 14:06:27 GMT

Re: Taking one response from a field

Mon, 02 Jun 2014 14:57:17 GMT

Can you paste the section of the load script?

So we can see in context?

Re: Taking one response from a field

Anonymous — Mon, 02 Jun 2014 15:15:03 GMT

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;

Re: Taking one response from a field

Jason_Michaelides — Mon, 02 Jun 2014 16:45:20 GMT

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