6 Replies Latest reply: Feb 6, 2013 6:15 AM by Muralidhar Koti RSS

    QlikView as a search engine for SQL database data

    Muralidhar Koti

      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
          Dave Riley

          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
              Muralidhar Koti

              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
                  Dave Riley

                  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
                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.