6 Replies Latest reply: Apr 18, 2018 5:33 AM by Ahmed Raza RSS

    Calling Subroutine

    Gora Costa Sanz
      Hi,
      for my current qlikview project I need to calculate the next working day for any given date.So far i have a master calendar with a field that indicates if it's a working day or holiday(including weekend).So i thought of coding a subroutine like this :
      SUB Next_Day (Date)
      end = 0;
      counter = 1;
      do while end=0
      Date=Date+counter;
      tipo=lookup(Festivo,Key_Date,Date,Master_Calendar);
      if tipo='Festivo' then;counter=counter+1; else;end=1;endif
      loop
      END SUB
      i'm not sure if it works as i'm unable to call it.In my ideal scenario I would use my routine in a load sentence like the rest of qlikview functions :
      load *,
      Next_Day(date) as Next_Day_Date
      resident master_calendar;
      but it gives error, i have tried to use it in a loop like this :
      LET Calendar_Rows = NoOfRows('Master_Calendar');
      for i=1 to $(Calendar_Rows) // loop through every row
      let Fecha = FieldValue('Key_Date',$(i));
      let Fecha_Alt = call Next_Day(Fecha);

      next
      but it doesn't work either.I also don't know how to write the field in the table , I would prefer doing it in the load sentence.After reading some community posts, I have come to the conclusion that is not possible to use subs in load sentences or inside loops, which leaves the subroutine sentece worthless to me.So my questions would be :
      Which is the correct way of calling a function and obtain the return value?
      Which are the limitations using subroutines?
      Is it possible to use them inside load sentences?
      Is it possible to use them inside loop sentences(do..while, for... next)?
      If it isn't possible to call subs from load sentences, how could I loop trough a date field in my table , use my routine, and store the new date in the same or another field?
      Thanks alot to anyone who can help me.....
        • Calling Subroutine
          Ahleak George

          I'm in a similar predicament as you are. Except in my case, I want to use a subroutine to apply transformations to fields in several different tables as they're loaded.

           

          The most elegant thing that popped into my head was to use a subroutine containing all of my transformations, and apply those to the fields within a load statement (the alternative is to use a bunch of nestled functions, or to use stacked load statements).

           

          I have my sub defined as follows.

           

          sub normalize_name (name)

              name = subfield(name, '_', 1); // remove numerals from end of resource name

              name = replace(normalized_name, ', ', ','); // remove the space following the comma in the resource name

          end sub

           

          Qlikview doesn't show any syntax errors with that, and on reload, it steps through that with no issues.

           

          I am trying to call it in the load statement as follows.

           

              load call normalize_name (resource) as normalized_name, *;

           

          But everything after the call statement on that line is underlined in red (indicating a syntax error). My load statement is above another load statement that provides the 'resource' field. The field name and capitalization is correct.

           

          Unfortunately, the reference manual doesn't really give too much information on subroutines (where are they valid to be used, etc). The syntax given for call in the reference manual and help docs is simple.

           

               call name ([ paramlist ])

           

          I'm assuming at this point that call is not valid within a load statement.

            • Calling Subroutine
              Gora Costa Sanz

              Hi lord,

                   Yes ,after several tests it's clear that it is not possible to call subroutines from a load sentece, at least in version 10.I've been able to call them for testing purposes outside them but its useless to me, and it seems that also to you, anyway thanks for sharing your thoughts and lets hope they improve the subroutines part to be more useful and also improve the documentation, which I also think it's a little bit obscure in some areas.

                • Calling Subroutine
                  Dave Riley

                  Hi,

                   

                  It looks like you should be able to do what you wanted in your first post by calling the sub in a loop similar to this code which converts Decimal numbers to Binary, the sub is setting the variable which is called by the loop ...

                   

                  Sub DecBin (Number) // Function to convert Dec to Binary

                    LET Output = Floor(Number/POW(2,26),1);

                    FOR i = 25 to 0 step -1

                     LET Remainder = Mod(Number,POW(2,$(i)+1));

                     LET Output =  Output & Floor(Remainder/POW(2,$(i)),1);

                    NEXT i;

                  End Sub;

                   

                  FOR x = 1 to NoOfRows('USERS') 

                    tmp:

                    LOAD userAccountControl as NumberSubmit resident USERS where RecRef = $(x);

                    Let tmpNumberSubmit = FieldValue('NumberSubmit',1);

                   

                    CALL DecBin($(tmpNumberSubmit));

                   

                    BinValues: // store Binary value into a temporary separate table for now

                    LOAD

                     RecRef AS RecBack,

                     $(Output) as BinaryValue

                    Resident USERS Where RecRef = $(x);

                   

                    DROP TABLE tmp;

                   

                  NEXT x;

                   

                   

                  This is fine if the table isn't too large to loop through.

                   

                   

                  flipside

                • Calling Subroutine
                  Dave Riley

                  Hi Lord,

                   

                   

                  I think in your case you would be better off using dynamically built scripts along the lines of this ...

                   

                  Data:
                  LOAD * INLINE [
                  name
                      FName1_LName1
                      FName2_LName2
                      FName3_LName3
                      FName4_LName4
                  ];

                  Data2:
                  LOAD * INLINE [
                  customer
                      Fcustomer1_Lcustomer1
                      Fcustomer2_Lcustomer2
                      Fcustomer3_Lcustomer3
                      Fcustomer4_Lcustomer4
                  ];

                  //Cleanse routine
                  SET vCleanseMaster =  subfield(xyz,chr(95),1); // Use ANSI reference (chr(95) rather than actual character, xyz is a holder value of your choice

                   

                  LET vCleanse1 = Replace('$(vCleanseMaster)','xyz','name');
                  CleansedData1:
                  LOAD $(vCleanse1) as CleansedName resident Data;

                   

                  LET vCleanse1 = Replace('$(vCleanseMaster)','xyz','customer');
                  CleansedData2:
                  LOAD $(vCleanse1) as CleansedCustomer resident Data2;

                   

                  flipside

                • Re: Calling Subroutine
                  Subhash Gherade

                  Qlikview Sub Routine

                  Few people know that qlikview code can be set up within sub routines. I find this to be a very helpful tool to use in situations where I may not want to reload the entire script. The most common use case for me is the QVD generator. Often times while I am developing, I only want to reload one or two QVDs and not the entire set. While I could comment and uncomment large portions of code or rearrange my script and use EXIT SCRIPT, I find that using sub routines are faster and cleaner.

                  Creating and using sub routines are easy. First, write you script as you normally would and then give your routine a name using SUB <name>. Then close your sub using END Sub.

                  SUB ProductLoad

                  Product:
                  LOAD
                  ListPrice,
                  Name as ProductName;
                  SQL SELECT *
                  FROM Product;

                  STORE Product INTO Product.QVD (QVD);
                  DROP Table Product;

                  END SUB;

                   

                  Once I have created my set of sub routines, I can use the CALL command to only run the ones that I want. It is important that the call command comes after the scripting of the sub routines. For this reason, I have all of my call commands as the last tab of my script. Now, I can simply comment and uncomment which sub routines I want to run without having to alter anything else in my load script. Only the sub routine that is being called will run and any other sub routines will simply be ignored.

                  //Call SalesLoad;
                  Call ProductLoad;
                  //Call CustomerLoad;

                    • Re: Calling Subroutine
                      Ahmed Raza

                      Subhash,

                       

                      How can we use these calls programmatically?

                       

                      Scenario:

                       

                      I had 02 QVDs and both have similar 'Calls Tab' but different sub routines. Now I have merged both QVDs and wrote all scripts in 01 QVD.

                      Previously my QVD-A runs on a schedule of 1 hour time difference and the other QVD-B runs after 24 hours daily. Now as I have consolidated both QVDs, I have to run some 'Calls' on 1 hour difference and the rest should be called as per QVD-B scenarios i.e. after 24 hours.

                       

                      Can I handle these Call dynamically?

                       

                      QVD-A:

                      Call ProductLoad;

                      Call ItemLoad;

                      Call CategoryLoad;


                      QVD-B:

                      Call ProductLoad_1;

                      Call ItemLoad_2;

                      Call CategoryLoad_3;


                      Consolidated QVD:

                      if(variable = 1 hour schedule) ;

                      Call ProductLoad;

                      Call ItemLoad;

                      Call CategoryLoad;

                      else(variable = 24 hours schedule) ;

                      Call ProductLoad_1;

                      Call ItemLoad_2;

                      Call CategoryLoad_3;


                      Above is the desired scenario.


                      Thanks,


                      Araza