Retrieving Database Values from within a document

There may be many reasons why you might want to get a value from outside of QlikView at any time, without having to reload your document (even partially).

The obvious method is to use macros to retrieve the data using ADO calls.

However, there is a function in QlikView that can do this - but only to retrieve one value (e.g. a record count). The function is called SqlValue.

The function takes 3 parameters:

- Connection
- Query
- Connection Type

Connection Type is only one of 'OLEDB' or 'ODBC'. If you leave it out, ODBC is assumed.

The Query is a string that should (although not absolutely required) return one row and one value. If it returns more, only the first value in the first row is returned.

The Connection is straightforward if using ODBC because you just use the predefined ODBC connection name. For OLEDB, you need to pass an OLEDB connection string. Now, this can be messy (as they can get quite long and include single quotes which can throw you a bit. For me, the easiest way is to set up a variable with the Connection string in it (perhaps it could be stored in the registry and retrieved using GetRegistryString!)

Once you have it, then you just call like this:

=Num(SqlValue(MyDatasource, 'SELECT Distinct Count(*) As MyCount From Orders', 'OLEDB'))

Note that I have used the Num function here to cast the return value as a numeric. The SqlValue function returns a dual so, if you want to display it, you need to cast as numeric or it will display as blank (no Text value in the Dual).

The query that you pass can be as complex as the target database supports and can use values from your QlikView document:

=Num(SqlValue(MyDatasource, 'SELECT Distinct Count(*) As MyCount From Orders Where CustomerID in (' & Concat(Distinct CustomerID, ',') & ')', 'OLEDB'))

Obviously, and this is flagged in the help file, if you have queries running in your document, they can slow things down while waiting for the database query to return.

Removing Fields with a Wildcard

One of my clients asked an interesting question. They will have a standard set of QVDs to load data from that may be used in various documents. In each document, they will also have a mapping table to mass rename the fields that they need for that particular document.

Now, with their dataset, they will be left with a load of fields that start with the word "Custom_" - sometimes some of these fields will be aliased, sometimes they will not be used. If they are not used, then they will want to drop the fields from the document (to keep things nice).

Unfortunately, QlikView does not accept a command like

DROP FIELDS "Custom*";

So, we can use a couple of functions that are available in the script to do this for us. Here is a script sample:

MyTable:
LOAD * INLINE [
F1, F2, F3, F4
1, 2, 3, 4
];

Rename Field F1 to NewField;
Rename Field F3 to NewField2;

Let i = 1;

Do While i <= NoOfFields('MyTable')

Trace Getting Field $(i) From MyTable;

Let FieldName = FieldName($(i), 'MyTable');

Trace FieldName = $(FieldName);

Let Command = If('$(FieldName)' Like 'F*', 'Drop Field $(FieldName);', '');

Trace Command = $(Command);

$(Command)

Let i = $(i) + If('$(Command)' = '', 1, 0);

Loop

So, I load 4 fields, rename 2 of them, then loop through all the fields looking for a match ('F*') for the original names and create a command to execute in the script. (This is also a good example of using a dynamically created command in a script!)

The reload window should look like this:

MyTable << INL47EA 1 lines fetched
Getting Field 1 From MyTable
FieldName = NewField
Command =
Getting Field 2 From MyTable
FieldName = F2
Command = Drop Field F2
Getting Field 2 From MyTable
FieldName = NewField2
Command =
Getting Field 3 From MyTable
FieldName = F4
Command = Drop Field F4

Note that you need to use a While statement here not a For loop. This is because once you drop a field, say Field #2, the next field becomes that field number - Field #3 becomes Field #2 - so you will end up skipping fields. The While statement prevents this.