topic Re: Apply map function in QlikView

Apply map function

Anonymous — Mon, 16 Jun 2014 18:56:59 GMT

I'm trying to use the apply map function to display the outcome of a loan. But I would like to be able to put a restraint on the function so that it shows only for loans with a certain label. I tried to put the applymap in an if-statement and got an error. Is this possible?

Re: Apply map function

swuehl — Mon, 16 Jun 2014 19:01:54 GMT

Could you post your current script snippet? And some sample input data and what you expect?

What error did you get?

Re: Apply map function

rbecher — Mon, 16 Jun 2014 19:02:25 GMT

Hi Ashley,

please post your script code to get helped.. An if nesting shouldn't be a problem at all. I guess you have a typo. However, it would be more sufficient to create a mapping table which contains inlo the needed labels for this case.

- Ralf

Re: Apply map function

Anonymous — Mon, 16 Jun 2014 19:05:57 GMT

This is how i currently have the code set up. But there is a variable I create after these tables and I want to narrow down the 'Ultimate Outcome' by that variable

MAP:

Mapping

LOAD acct_nbr, //Loading the account numbers from the excel sheet along with the 'UltimateOutcome' field it includes

UltimateOutcome

FROM

//Integrating an excel sheet to use its contents

(ooxml, embedded labels, table is Sheet1);

//Creating a new table

LoanLoss:

Load

acct_nbr,

date(min(prod_dt)) as [Date First 90+], //Taking the min (earliest) production date for an acct that can be found so we know the first date it became 90+

1 as dateFirst90Count,

APPLYMAP('MAP', acct_nbr, ' ?? ') as UltimateOutcome //Using the mapping function to load the 'UltimateOutcome' variable into the LoanLoss table, only for the accounts located in the loanloss table though

Resident Loans //Taking fields from the 'Loans' table

where is90 = 1 //Only looking at the accts that have ever been 90+ at least once

group by acct_nbr; //Grouping by acct number so as to only get one row per acct number

Re: Apply map function

swuehl — Mon, 16 Jun 2014 19:14:53 GMT

Ashley, what error did you get?

I would assume something with an aggregation missing?

Re: Re: Apply map function

rbecher — Mon, 16 Jun 2014 19:21:47 GMT

Maybe with an aggragation here:

APPLYMAP('MAP', min(acct_nbr), ' ?? ') as UltimateOutcome

Though never used an applymap in an aggregation load.. If this doesn't work you could add a preceeding LOAD:

LoanLoss:

Load *, APPLYMAP('MAP', acct_nbr, ' ?? ') as UltimateOutcome;

Load

acct_nbr,

date(min(prod_dt)) as [Date First 90+], //Taking the min (earliest) production date for an acct that can be found so we know the first date it became 90+

1 as dateFirst90Count,

min(acct_nbr) as acct_nbr,

accounts located in the loanloss table though

Resident Loans //Taking fields from the 'Loans' table

where is90 = 1 //Only looking at the accts that have ever been 90+ at least once

group by acct_nbr; //Grouping by acct number so as to only get one row per acct number