6 Replies Latest reply: Apr 25, 2017 4:49 AM by Peter Cammaert RSS

    Calling Subs within Loads to return value

    Tom Neal

      Hi All,

       

      I am working on a solution and have put the pseudo code below:

       

      Subroutine 1

      Define vResc as Number

      ApplyMapLOT Site

      Define vSite as Site

       

      Subroutine 2

      Define vResc as Number

      ApplyMapNUMBER Site

      Define vSite as Site

       

      If Number = vResc then vSite

      else Call Subroutine 1 (returns vSite)

      else Call Subroutine 2 (returns vSite) as Site.

       

      My aim is to give all the same numbers the same site regardless if a lot exists or not.  Desired outlook is in the table.

      Subroutine 1 does the applymap on the log to return the site.  It also assigns the number to a variable and the site to a variable.

      Subroutine 2 does the applymap on the number to return the site.  It also assigns the number to a variable and the site to a variable.

       

      If the next number is equal to the vResc variable then the vSite variable is used as the site for that row.

       

      NumberLotSite
      1abc123London
      1abc456London
      1London
      2acb213Paris
      3cba321Madrid
      3Madrid
      4Rome

       

       

      Hope this makes sense - Subroutines might not be the right way to code this but not overly experienced in QV.

       

      Code below:

       

      Let vResc = '';
      Let vSite = '';

       

      Sub AssignSite1

      Let vResc = Number;

      SiteMethod:
      LOAD if(not ISNULL(Lot),ApplyMap('PSMAP', Lot, 'Lot Without Map')) as MfgSite
      ;
      Let vSite = MfgSite;

      ENDSUB;


      SUB AssignSite2

      Let vResc = Number;


      if(isNull(Lot), ApplyMap('MaskMapOne', Number, 'Multi-Site')) as MfgSite;

      Let vSite = MfgSite;

      ENDSUB;


      Map_Applied:
      Load
        Number,  
        Lot,  
        Description,    
           
            if(Number = vResc, vSite,
            if(Number <> vResc, CALL AssignSite1, CALL AssignSite2 )) as MfgSite
           
             Resident Stock_Data;

      Drop Table Stock_Data;

       

      Thanks,

      Tom

        • Re: Calling Subs within Loads to return value
          Anna fuksa

          COuld you post your app ?

           

          and try

          Map_Applied:

          Load

            Number,  

            Lot,  

            Description,    

               

                if(Number = vResc, vSite,

                if(Number <> vResc, if(not ISNULL(Lot),ApplyMap('PSMAP', Lot, 'Lot Without Map'))

                ,if (isNull(Lot), ApplyMap('MaskMapOne', Number, 'Multi-Site'))  )) as MfgSite

               

                 Resident Stock_Data;

          Drop Table Stock_Data;

           

          you are not suppose to use subroutine inside the expression.

          you can also use function pick instead.

            • Re: Calling Subs within Loads to return value
              Tom Neal

              Thank you for your response.

              I can post the app but there is little more detail on it than what I've posted and I can't post the full app because of sensitive data.

               

              I have thought of your solution however How will the variable be assigned a new value?

               

              The next number in the source table will always be different so the second condition in the if statement will always be met which is not the required outcome.

               

              I need the variable vResc to change to with every new number for this method to work.

                • Re: Calling Subs within Loads to return value
                  Anna fuksa

                  I want to see the data and be able to test my solution

                   

                  I just look at this and make a changes to code but was not able to test it so may have some little problems -it was just to give you idea.

                   

                  for variable you can choose function Peek or maybe you  may need to create function  for this case

                  its a little difficult to say now


                  but it can be easier if you will post as well expecting result with current app

              • Re: Calling Subs within Loads to return value
                Peter Cammaert

                You cannot CALL SubRoutines from inside a LOAD statement, you can only use functions (just like any QlikView function). SubRoutines don't return values, only functions do.

                 

                Redefine your code as a set of macro functions that return values and use them - without the CALL keyword - in a LOAD statement.

                 

                BTW CALL is a statement keyword. You cannot embed other statements in a LOAD...