topic Re: Include all subroutines from directory in QlikView

Include all subroutines from directory

nigel987 — Sun, 12 Apr 2020 15:58:50 GMT

Hi,

I have a directory where all my subroutines are stored. I want to load them all at once, so that I can call every subroutine without the necessity to include the text file with the subroutine first.

When I use Version A in the script (without a loop), everthing is fine:

//Version A:

$(Include=z:\documents\functions\CreateCalendar.txt);

Call CreateCalendar ('01.01.2018',Today());

But when I use the loop, it get an error (Syntax error Call >>>>>>CreateCalendar<<<<<< ('01.01.2018',Today()))

//Version B:
SET pathDataOrigin = 'z:\documents\functions\';

FOR Each File in filelist ('$(pathDataOrigin)'&'CreateCal*.txt')

$(Include=$(File));

Next File;

Call CreateCalendar ('01.01.2018',Today());

I cannot find the reason for the error. Can anyone help me with that?

The subroutine is very simple:

SUB CreateCalendar (StartDate, EndDate)

LET vDateMin = Num(StartDate);
LET vDateMax = Num(EndDate);

Calendar:
LOAD
Date($(vDateMin) + RowNo() - 1) AS Date
AUTOGENERATE 1
WHILE
$(vDateMin)+IterNo()-1<= $(vDateMax)
;

End Sub;

Re: Include all subroutines from directory

alex00321 — Mon, 13 Apr 2020 02:15:39 GMT

Hi, I mocked up your codes in my PC and i think the issue is happened with your sub. I updated a little and it could return data for me. Also in your default date format setting, I updated to this: SET DateFormat='MM.DD.YYYY';

SUB CreateCalendar (StartDate, EndDate)

LET vDateMin = NUM(StartDate);
LET vDateMax = NUM(EndDate);

Calendar:
LOAD
Date('$(vDateMin)' + RowNo() - 1) AS Date
AUTOGENERATE 1
WHILE
'$(vDateMin)'+IterNo()-1<= '$(vDateMax)'
;

End Sub;

Thanks!

Re: Include all subroutines from directory

albertovarela — Mon, 13 Apr 2020 04:43:02 GMT

A quick workaround is to create an include where you include all you functions.

I am taking this example from the QDF:

A qvs including all you functions

Including the previous on your App

I hope this helps

Re: Include all subroutines from directory

alex00321 — Mon, 13 Apr 2020 06:04:07 GMT

I'm not sure what happened to my answer but it was removed somehow. So I re-posted here,

SUB CreateCalendar (StartDate, EndDate) LET vDateMin = NUM(StartDate); LET vDateMax = NUM(EndDate); Calendar: LOAD Date('$(vDateMin)' + RowNo() - 1) AS Date AUTOGENERATE 1 WHILE '$(vDateMin)'+IterNo()-1<= '$(vDateMax)' ; End Sub;

I updated your sub and it's working for me now, also you need to watch out your date format to change to below:

SET DateFormat='MM.DD.YYYY';

Thanks!

Re: Include all subroutines from directory

nigel987 — Mon, 13 Apr 2020 17:00:06 GMT

Hi, the sub itself was working as expected without the changes. But also with your changes, inserting it with the loop and then calling it leads to the described error.

Re: Include all subroutines from directory

nigel987 — Mon, 13 Apr 2020 17:09:30 GMT

Hi,

thanks for this solution, but the workaround is not my prefered option, as I would state all function names explicitly when adding new subs. And when deleting subs, I would need to remove it from this list as well, ... I'd prefer a solution where changes don't need further effort.

Thank you!

Re: Include all subroutines from directory

atoz1158 — Tue, 14 Apr 2020 11:52:22 GMT

Version B should be as follows

//Version B:
SET pathDataOrigin = 'z:\documents\functions\';

FOR Each File in filelist ('$(pathDataOrigin)CreateCal*.txt')

$(Include=$(File));

Next File

Call CreateCalendar ('01.01.2018',Today());

HTH

Regards

Adrian

Re: Include all subroutines from directory

marcus_sommer — Tue, 14 Apr 2020 13:19:39 GMT

Your logic itself should work - just run it within the debugger with a break point below the loop and then you could see within the various debugger-tabs your include-variables. I could imagine that you find the routine CreateCalendar or any other one several times …

- Marcus

Re: Include all subroutines from directory

nigel987 — Tue, 14 Apr 2020 17:18:07 GMT

Hi,

I still get the same error. Did it work for you?

Kind Regards, Nigel

Re: Include all subroutines from directory

nigel987 — Tue, 14 Apr 2020 17:22:12 GMT

Hi,

I've done that, and no, the routine just appears this one time.

The debugger itself is not very helpful in this case.

Re: Include all subroutines from directory

marcus_sommer — Wed, 15 Apr 2020 07:00:11 GMT

There is no breaking point (right click on the row-number will set/remove one) set and therefore it just runs into an error without giving you the possibility to monitor what's loaded within the include-file - you noticed that there is now another tab above the debugger-script-window?

Because of the fact that this tab/window is created and is called CreateCalendar I assume that the include-file is really loaded - in the most cases it's exactly the reason (an invalid path) why anything didn't work. Nevertheless you may to ensure that it worked like expected and if not to trace the issue - for this you should use must_include instead of include and TRACE $(File);.

Therefore I think that there is something wrong with your sub-routine probably any small syntax-issue, for example I noticed that there is a space between the routine-name and the parameter (I'm not sure if it lead to an error) ...

- Marcus

Re: Include all subroutines from directory

nigel987 — Thu, 16 Apr 2020 16:58:34 GMT

Hi @marcus_sommer ,

you're right, you can't see a breaking point in the screenshot, because I debugged step-by-step. And yes, I noticed the new tab with the sub, hence I assume that the sub is loaded properly.

And I know that the sub itself is working, because when I include this file only (Version A) it works as expected.

//Version A:
$(Include=z:\documents\functions\CreateCalendar.txt);
Call CreateCalendar('01.01.2018',Today());

But still, when using the loop, I cannot get it running, and the debugger isn't helpful either.

SUB CreateCalendar(StartDate, EndDate)

LET vDateMin = Num(StartDate);
LET vDateMax = Num(EndDate);

Calendar:
LOAD
Date('$(vDateMin)' + RowNo() - 1) AS Date
AUTOGENERATE 1
WHILE
'$(vDateMin)'+IterNo()-1<= '$(vDateMax)'
;

End Sub;

Re: Include all subroutines from directory

marcus_sommer — Fri, 17 Apr 2020 09:15:56 GMT

I just dived a bit deeper in the matter and think you are right that it's not working in this way - without that there are "obvious" logically or syntactically errors. Every part "seemed" to be loaded and executed in the right order.

But it's not completely true. The cause of the sub-error here is that the sub-routine was created within a control-statement.

Control-statements like sub … end sub, if … end if, for … next have special properties in regard to their validity. This means either the existence and execution-order of them as well as the objects (tables, variables, further control-statements) which are created within them. This means they aren't available from everywhere.

In your case it means the sub was created within the for-loop and is only within the loop available and each call from the outside called a non exists statement.

I don't know if it's anywhere sufficient documented how Qlik has implemented the control-statements and which requirements are following from it. I remember that I have seen already postings where the guru's discussed this topic - maybe Rob Wunderlich could shed some light on the matter.

In the end I doubt that there is a way to apply your mentioned approach and that the already made suggestion of applying multiple (nested) include-variables is probably the most suitable solution (I use this approach since many years and it's IMO quite practical).

Nevertheless I tried to develop a workaround with a logic which loads these include-files within a table, aggregated the records, creates multiple variables within a loop, called them outside from the loop, executed the routines and cleanes everything. It worked - but it's not really simpler and you will further need to specify each variable … but maybe somebody has further ideas to make this snippet more useful:

set pathDataOrigin = 'YourPath\';

for each File in filelist ('$(pathDataOrigin)CalendarIncludeT*.txt')
t1: load [@1:n] as Statement, filebasename() as Filebasename, rowno() as RowNo from [$(File)] (fix, codepage is 1252);
next File

t2: load Filebasename, concat(Statement, chr(10), RowNo) as Statement resident t1 group by Filebasename;
drop tables t1;

for i = 0 to noofrows('t2') - 1
let s$(i) = peek('Statement', $(i), 't2');
$(s$(i));

$(s0);
$(s1);

call CreateCalendar('01.01.2018',Today());
call CreateCalendarMonth('01.01.2018',Today());

for i = 0 to noofrows('t2') - 1
let s$(i) = null();
next

let i = null();

Just came another thought that the above mentioned control-statements are the most usual ones but there more available like do while loop, case switch, … and maybe there are any differences how their validity worked ... in the case you want a bit play with them ...

- Marcus

Re: Include all subroutines from directory

nigel987 — Sat, 18 Apr 2020 17:17:47 GMT

Hi @marcus_sommer ,

many thanks for your elaborate explanation. I wasn't aware of the fact, that control statements behave in this way. Now I understand why the workaround as suggested by @albertovarela is so extensively used, and I guess it is okay to use it, as it can be expected that functions are not changed very often. Understanding the reason behind this solution makes it even more acceptible. Thanks for shedding light on this topic.

Kind Regards,

Nigel