Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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'?
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.
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.
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
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