Include scripts

[brindlogcool]

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.

[jagan]

Hi,

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

Regards,

Jagan.

[#3]

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.

[Gabriel]

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

[stevedark]

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/

[#6]

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.

[stevedark]

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/

[ToniKautto]

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);

[#9]

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; 

[brindlogcool]

Thanks Toni,

Very useful information, i have never used must keyword.