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
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.
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.
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);
FOR x = 1 to NoOfRows('USERS')
LOAD userAccountControl as NumberSubmit resident USERS where RecRef = $(x);
Let tmpNumberSubmit = FieldValue('NumberSubmit',1);
BinValues: // store Binary value into a temporary separate table for now
RecRef AS RecBack,
$(Output) as BinaryValue
Resident USERS Where RecRef = $(x);
DROP TABLE tmp;
This is fine if the table isn't too large to loop through.
I think in your case you would be better off using dynamically built scripts along the lines of this ...
LOAD * INLINE [
LOAD * INLINE [
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');
LOAD $(vCleanse1) as CleansedName resident Data;
LET vCleanse1 = Replace('$(vCleanseMaster)','xyz','customer');
LOAD $(vCleanse1) as CleansedCustomer resident Data2;
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.
Name as ProductName;
SQL SELECT *
STORE Product INTO Product.QVD (QVD);
DROP Table Product;
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.
How can we use these calls programmatically?
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?
if(variable = 1 hour schedule) ;
else(variable = 24 hours schedule) ;
Above is the desired scenario.