topic Re: QlikView as a search engine for SQL database data in QlikView

QlikView as a search engine for SQL database data

Fri, 01 Feb 2013 11:46:14 GMT

Hi,

I have got a requirement to create a search engine in QlikView to browse through the data that is available in our data warehouse tables (around 150 tables).

The below is what I have managed to do till date

1. Load all tables data with 'Qualify'. That way they are not joined to each other (and I dont want them to be joined)

2. A list box on the top which displays the list of table names.

Now I need to do the below, but not sure what is the best way to do the same

3. Create a search object and assign it with the fields of the selected table (from the list box defined in step 2 above)

4. Create a table object which will display all columns of the selected table.

I want both point 3 & 4 to change dynamically based on table selected in step 2 above.

Is this possible in QlikView? if YES then please help me to understand the best way to do the same.

Regards,

Murali

Re: QlikView as a search engine for SQL database data

flipside — Fri, 01 Feb 2013 12:04:24 GMT

Hi Murali,

You can get to the database schema using SQLTABLES, SQLCOLUMNS and SQLTYPES. I use these commands to load the schema into tables, but you may have to do it a certain way via resident tables if you need to filter them. Something like ...

ODBC CONNECT TO ???;

AllODBCTables:

Load *;

SQLCOLUMNS;

ODBCDataTypes:

Load *;

SQLTYPES;

DISCONNECT;

...

AllTables: // for investigation into all available tables

LOAD

//TABLE_QUALIFIER as All.tblQualifier, //no data

TABLE_OWNER as All.tblOwner,

TABLE_NAME as All.tblName,

COLUMN_NAME as All.tblColName,

DATA_TYPE as All.tblColDataType,

//TYPE_NAME as All.tblColTypeName, //no data

PRECISION as All.tblColPrecision,

LENGTH as All.tblColLength,

SCALE as All.tblColScale,

RADIX as All.tblColRadix,

NULLABLE as All.tblColNullble,

//REMARKS as tblColRmrks, //no data

//EXTENDED_ID as tblColExtID, //no data

DATA_SOURCE as All.tblDSource

resident AllODBCTables

left join (AllTables)

LOAD

dtDataType as All.tblColDataType,

dtTypeNameList as All.dtTypeNameList

resident ODBCTypes;

If you then want to interrogate data within the fields, you can probably build a macro to do SQL interrogation via ADODB based on user selections or maybe the new Direct Discovery can help there. Another way is to just loop through each table in the load script with Qualify (as you stated) and load * (but this might create a very large file).

flipside

Re: QlikView as a search engine for SQL database data

Fri, 01 Feb 2013 12:17:16 GMT

Thanks for the response.

The data inside the tables are not that huge so the size is not a problem. I have used the Qualify * and is loading the data as desired.

The challenge that I am having is

1. How to dynamically assign columns of a selected SQL table to 'QlikView table object'?

Re: QlikView as a search engine for SQL database data

Gysbert_Wassenaar — Fri, 01 Feb 2013 12:29:04 GMT

2. Add a listbox and add the system field $Table

3. Create a search object and select Search in List of Fields and use the expression =GetFieldSelections([$Field])

4. You'll have to use a macro if you want to dynamically create a table box with all the columns of a table.

Sub ReplaceTBFields

Set Fields = ActiveDocument.Fields("$Field").GetPossibleValues

Set tb= ActiveDocument.GetSheetObject("TB07")

tb.addfield "$Field"

for i = 0 to tb.getcolumncount -1

tb.removefield(i)

for i = 0 to Fields.Count -1

tb.AddField Fields(i).Text

tb.removefield(0)

end sub

Change TB07 with the ID of your table box.

Re: QlikView as a search engine for SQL database data

flipside — Fri, 01 Feb 2013 13:08:12 GMT

Okay, understand it now.

Create yourself the following variables ...

objFieldList = Concat($Field,';') -- assumes none of your fields have ';' char in them

objField1 = RangeMaxString(subField(objFieldList,';',1),0) -- or use a space instead of zero

objField2 = RangeMaxString(subField(objFieldList,';',2),0)

... and repeat until enough variables for fields as required. This could easily be created in a loop in the script.

Then, in a chart object create calculated dimensions and a dummy expression (=1) as ...

=$(objField1)

=$(objField2) .. etc

flipside

EDIT: Ignore the RangeMaxString above, you need to wrap some control around the Calculated dimension to hide the column when that variable is not relevant - will work that out.

Re: QlikView as a search engine for SQL database data

flipside — Fri, 01 Feb 2013 13:18:46 GMT

I'll try that again ...

The variables:

objFieldList = Concat($Field,';')

objField1 = subField(objFieldList,';',1)

objField2 = subField(objFieldList,';',2)

... and repeat until enough variables for fields as required.

The calculated dimensions:

='$(objField1)'

='$(objField2)' .. etc

flipside

Re: QlikView as a search engine for SQL database data

Wed, 06 Feb 2013 11:15:01 GMT

Hi,

Thanks for your response.

Points 2 & 4 works perfectly. WHere as point 3 didn't work for me.It says 'No matches found in search'.

Also in Point 4, is there any way I can modify the 'Label' for the table object field. As I have used Qualify *. table names are coming in the field names. I want to replace the tablename. with "".

Thanks in advance for your help.

Regards,

Murali