help - connection between 2 list box

[#1]

Hello

I would like to make a connection between 2 or more table and showing them by conditional expression.

I've data base that include 5 tables : (2 for the metadata, 3 for the real data) :

table_name - {table_id,table_name} - this table save the "metadata" on the table and it have 3 rows of data like ([1,students],[2,courses],[3,faculty])

column_name -{column_id,table_id,column_name} - this table save all the column in the 3 tables (20 rows...)

this two thable are connected in the data base.

now what I want to do is to show them - thats easy - I make a list view for them (I need only the names so i choosed - table_name & column_name) and I see them and when I choose in the list own of the rows I can see the connection. till here there isn't a real problem.

what I need is the connection to the real data, I'll explain :

if I choose in the listbox called 'table_name' the value "students" I want to see a table that contain the fields in 'students' table and all the data that they have. the same for the other fields...

but i don't want to do 3 conditional if, because I want to extend my database and I want to do it dynamicly.

now after i choose a table to show and its all data I want to Drill-down.

i want to choose 1 field in the listbox called 'row_name' the value "ID_student" or "student_name" or "student_address" etc... and I want to see a list box of the data that this fields have in the table.

again i don't want to write a lot of conditionals if, because I want to do it dynamicly.

I hope that it was understandable.

I tried a lot of ideas and qlikview is a little bit restricted.

[Oleg_Troyansky]

Danny,

in a nutshell, you'd need to do a lot of programming in VBScript macros to develop a dynamic screen like this one. If you really want to do it, download "QlikView Explorer" as an example - it allows building dynamic "tables" with selected fields. You can get some ideas there.

I'd argue the advantages of such approach alltogether - QlikView is already a "high-level" development tool. You are trying to develop a dynamic "screen generator" on top of the existing "screen generator"...

I don't think you'll eve be able to save the amount of time that you need to invest up-front to develop something like this...

just my opinion,

Oleg

[#3]

Hi Oleg

thank you for the response.

I spent a lot of time to understand the software capabilities because this part of the program is part of my big project for my graduate degree so I need to solve this.

I understand that I need to do a programming in VBs macro and I've a lot of ideas,

but I've some problem - the menual don't cover all the basic syntax for qlikview object , and I need to pass one by one in the example to understand the vb script.

I do know how to program in VB but I don't understand the hirarchy / the functions that qlikview covers.

do you know if there a menual/internet site only for qlikview vb functions/objects

10X

[Anonymous]

Trying a way to load the apriory unknown tables from the database using the metadata table "table_name", and keeping all linked. The table "columns" here is not necessary, but I keep it for "completeness", just in case:

tables:
SQL SELECT
 table_id,
 table_name
FROM table_name;
LET NumOfTables = NoOfRows('tables');
columns:
SQL SELECT
 column_id,
 column_name,
 table_id
FROM column_name;
for T = 1 to NumOfTables
 LET TableName = peek('table_name', $(T)-1, 'tables');

 $(TableName):
 SQL SELECT
 '$(TableName)' as table_name,
 *
 FROM (TableName);
next

I think it eliminates most of the possible macro troubles.
As for the help on VB macros, I don't think there is anything more complete than APIGuide.qvw, plus this forum. And maybe some blogs, links to which also can be found here on this site

[Oleg_Troyansky]

MIchael,

The macro will be needed for presenting the tables "dynamically" on the screen. I wasn't even addressing the load process.

Danny - APIGuide.qvw is indeed the best reference of all QlikView APIs. It's installed as a part of the "Complete" installation under \Program Files\QlikVIew\Documentation.

Oleg

[Anonymous]

Oleg, I aslo understood that the macro should be able to present the table dynamically. I'm suggesting the data model that can make the macro maybe two orders of magnitude simpler, no any conditional branching. Without going into the details (at least for now), here is the possible way to go:
1. Front end contains a listbox with the name of the data tables. Property - only one field is alwyas selected.
2. Front end also contains a straight table with the table fields as dimensions, and one expression which is
- hidden
- independent on selected table.
(It could expression =1)
3. User selects another table name in the list box. This action triggers the macro.
4. Macro removes all dimensions from the straight table and adds the fields of the selected table as dimensions.
It shouldn't be a complex task. Not sure though if it covers all the requirements.

[#7]

Hi Michael

thanks for the help

the code was very helpful for the script understanding.

also your last post was given me some idea to do it. (I need to get dirty with VB..... :-))))) )

now I search for deep understanding the qlikview VB by review the APIguide.qvw

do you think that for complex drill-down in the data (e.g. the field selction as I mentioned in my original post) I should do exact as you told to do with the tables?

if you an idea/example that you know I will glad to know.

thanks again 🙂

P.S.

Oleg , thanks for mention the APIguide.qvw , it realy give me a big insight on qlikview automation

Danny

[Anonymous]

Danny,
When you say that the "code was very helpful for the script understanding", it tells me that I was driving in the wrong direction. This is not a "normal" script for the data analisiys application, you can call this script rather a "database tables viewer". It reads the metadata, and based on it loads all actual data tables linked to the list of tables. I thought that this was what you wanted.
When you build a business inteligence application, database structure is one of the inputs, a part of the requirements. Based on the business part of the requirements, you decide what to load from the database. This decison cannot be automated because the business requirements gathering cannot not be automated. A "normal" script will may look more like this:

students:
SQL SELECT
 student_id,
 student_name,
 student_city,
 ***
FROM students;
courses:
SQL SELECT
 course_id,
 course,
 ***
FROM courses;
faculty:
SQL SELECT
 faculty_id,
 faculty_name
 ***
FROM courses;
***

As for the macros, they are optional part of the QV aplications. They certainly can be useful, often necessary, but it's a good practice to aviod macros whenever possible.
Please take a close look at the tutorial and the QV examples that come with the installation (if you use "complete install").

[#9]

Hi Michael.

I didn't look for driving you guys in a wrong direction.

my primary goal is to make a program that can get any database and analyse it for the data manager.

I need to take the database and explore every table and make some manipulation on the fields and then to make a great presentation.

your code was very helpful and I'm using it in my code because I need to load a lot of tables (depend on the database) and this part of code can load for me all the tablse in the database in a short way and not like the long way like you write in your last post.

now, I need the macro for taking the tables fields and show them to the user when he press on one of them.

in the main GUI screen I want that the user can select tables/fields in the table and get the data + I want to show him som graph chart that showing all the mnipulations that I did to the database. (all the manipulations were added to some QVD filse and read by QV when I runing the script.

I will glad to here about examples that maybe going to this direction so I could learn from them and become an "expert" in QV.

10X again.

[Anonymous]

Danny,
If this is really what you need - see attched example. It's using excel as data source, which is attached too. Hope it will help.
Good luck!