topic Re: Include scripts in QlikView

Include scripts

brindlogcool — Mon, 04 Mar 2013 15:46:04 GMT

Hi,

I need to connect to 3 different database. I need to put the connection string in the include script. Do i need to create three text files or is there any way to include with if else condition.

Re: Include scripts

jagan — Mon, 04 Mar 2013 15:54:54 GMT

Hi,

You can use If - Else condition for this in script, check for syntax in Qlikview Help file.

Regards,

Jagan.

Re: Include scripts

Mon, 04 Mar 2013 16:08:03 GMT

Create a subroutine which takes the parameter and based on the parameter return the proper connection string. This will be handy and in future if you want to connect to new DB you just need to modify only one place.

Re: Include scripts

Gabriel — Mon, 04 Mar 2013 21:48:11 GMT

Hi,

In my limited experience, if feel comfotable with 3 text file. This is easy to maintain and any future changes could be easily managed. If you wan to use them in the script IF function is very handy to the rescue.

Regards,

Gabriel

Re: Include scripts

stevedark — Mon, 04 Mar 2013 22:28:32 GMT

Hi there - you can put IF / ELSE logic into an include file, so you could have all three connection strings in a single include file and pick the right one out at run time.

You may want to see my blog post on include files for further information: http://bit.ly/YGkMUY

- Steve

http://www.quickintelligence.co.uk/

Re: Include scripts

Sat, 30 Mar 2013 12:20:59 GMT

Hi Steve,

I am new to QlikView and the concept of INCLUDING of external text file into script inside QVW file is very interesting for me and I know about it today - from your (http://bit.ly/YGkMUY). Thanks!

But when I have read your sentence "QlikView will not raise an error if your include file is not found – it will simply continue without doing anything.", I have started to experiment with it and then I have find some SMALL "finesse" how to solve it, if the text file will be contain only one LOAD (for one table).

The principle of my solution is build on the fact, that we can define a list of table names of some table before LOAD statement and QlikView will use the last occurance of it as user table name and the previous occurences will ignore without any error messages.
.
So, if we write
A_table:
B_table:
C_table:
LOAD ...;

the QlikView will give to the table inside LOAD the C_table name (not A_table name and not B_table).

And here is my small example of using this principle:

1) I define some script in the text file, for example load_ABC.txt:
ABC:
LOAD * INLINE [
    A, B, C
    1, 10, 100
    2, 20, 200
    3, 30, 300
];

2) Inside QVW file I define script with this two lines:
ABC_PROBLEM:
$(include=load_ABC.txt);

3) I run RELOAD with a standard way (with Ctrl+R), not in debug mode:
3a) When load_ABC.txt exists, then QlikView will go further without any error message
and the table from txt file will be named like ABC.
3b) But when load_ABC.txt does not exist, QlikView will display this error message:

Unknown statement
ABC_PROBLEM

So it is all. Yes, I know - it is very single case but maybe somebody else can use it.

Re: Include scripts

stevedark — Sat, 30 Mar 2013 17:32:09 GMT

Hi there - thanks for your thoughts on this and for taking the time to read my blog post.

Two ways that could be used to check for the existence of the include file is variables, where a variable is set in each include file, eg:

set vIncludeFound = 0;

$(Include=MyIncludeFile.qvs);

if vIncludeFound = 0 then

Trace Include File: MyIncludeFile.qvs Missing;

Exit Script;

end if

Or your could check for the existence of the file:

if FileSize('MyIncludeFile.qvs') > 0 and not isnull(FileSize('MyIncludeFile.qvs')) then

$(Include=MyIncludeFile.qvs);

else

Trace Include File: MyIncludeFile.qvs Missing;

Exit Script;

end if

I must confess I tend to just ensure that the file is in the right place, and deal with any errors that occur should anyone accidentally delete it.

An exception being thrown on the file not being there would be preferable though.

Cheers,
Steve

http://www.quickintelligence.co.uk/

Re: Include scripts

ToniKautto — Sat, 30 Mar 2013 19:08:31 GMT

I would go with separate files for each connectstring, as this should be easier to maintain and overview. Naming the files intuatively to show which source they are for should also make the usage transparent in your script.

If you are concerned about the files being unavailable, there is an option that will give you an reload error for missing files. Use the keyword must_include instead of include, if you want Qlikview to validate the file existance.

$(must_include=scriptFile.qvs);

Re: Include scripts

Sun, 31 Mar 2013 01:18:58 GMT

Very informative discussion. I to would also recommend going with a seperate include files for each connect string. Must admit I was not aware of this "must_include" option that Toni presented. Taking everything into account, may I suggest something similar to this logic:

SUB DATABASE_CONNECTIONS (box)
LET my_include= '..\..\Include\MyConnections\odbc-connect-' & '$(box)' & '.qvs';
$(must_include=$(my_include));
END SUB;

BOX:
LOAD * INLINE [BOXName
"BXA"
"BXB"
"BXC"
"BXD"
"BXE"
"BXZ"
];
LET vNumBoxes=NoOfRows('BOX');
FOR i=0 to $(vNumBoxes)-1
   LET vBox=Peek('BOXName',$(i),'BOX');
   LET vVolCnt=peek('VOLCnt',$(i),'BOX');
   CALL DATABASE_CONNECTIONS (vBox);
NEXT;

Re: Include scripts

brindlogcool — Sun, 31 Mar 2013 02:30:16 GMT

Thanks Toni,

Very useful information, i have never used must keyword.

Re: Include scripts

stevedark — Sun, 31 Mar 2013 22:30:15 GMT

Thanks Toni, I have a lot of Include statements to update - and a blog post to amend.

- Steve

Re: Include scripts

Mon, 01 Apr 2013 12:33:10 GMT

Hi Steve,

thank you very much for your two tips for direct and also undirect detect of existence of include file.

Mirek

Re: Include scripts

stevedark — Tue, 02 Apr 2013 23:47:38 GMT

That updated blog post, relating to Include files can be found here: http://bit.ly/11jdwzz

- Steve

Re: Include scripts

rwunderlich — Wed, 03 Apr 2013 13:01:55 GMT

Hi Toni,

Must_Include is great! When was it introduced? I don't find any reference in the Help or Ref Guide.

-Rob

Re: Include scripts

Anonymous — Sat, 06 Apr 2013 09:14:59 GMT

Dear All

This Must_Include() function does indeed look useful.

However does anybody know if such an undocumented function would be formally supported by QlikTech and will it remain available in futre QlikView Releases ?

Best Regards,

Bill

Re: Include scripts

ToniKautto — Mon, 28 Oct 2013 19:14:05 GMT

FYI, the must_include is now part of the QlikView reference manual.

Re: Include scripts

stevedark — Mon, 28 Oct 2013 20:27:00 GMT

Hi Toni,

That's good to know about must_include, I can now recommend it with more confidence that it is not going to disappear at some future point.

Steve