4 Replies Latest reply: Mar 5, 2012 6:50 AM by Dave Riley 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