5 Replies Latest reply: Jun 16, 2014 3:21 PM by Ralf Becher RSS

    Apply map function

    Ashley Navin

      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
          Stefan Wühl

          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
            Ralf Becher

            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
                Ashley Navin

                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

                [C:\Users\anavin\Desktop\UltimateOutcome.xlsx]  //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
                    Stefan Wühl

                    Ashley, what error did you get?

                     

                    I would assume something with an aggregation missing?

                    • Re: Re: Apply map function
                      Ralf Becher

                      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