topic Calling Subroutine in QlikView

Calling Subroutine

Anonymous — Fri, 17 Feb 2012 09:38:40 GMT

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

Fri, 02 Mar 2012 22:59:28 GMT

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

Anonymous — Mon, 05 Mar 2012 11:03:40 GMT

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

flipside — Mon, 05 Mar 2012 11:19:27 GMT

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

flipside — Mon, 05 Mar 2012 11:50:11 GMT

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 — Wed, 31 Jan 2018 07:10:36 GMT

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

Anonymous — Wed, 18 Apr 2018 09:33:49 GMT

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