QlikView as a search engine for SQL database data

[#1]

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

[flipside]

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

[#3]

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'?

[Gysbert_Wassenaar]

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)

     next

     for i = 0 to Fields.Count -1

          tb.AddField Fields(i).Text

     next

     tb.removefield(0)

end sub

Change TB07 with the ID of your table box.

[flipside]

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.

[flipside]

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

[#7]

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