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;