topic Create list boxes with the list of Tables and associated fields in QlikView

Create list boxes with the list of Tables and associated fields

Fri, 08 May 2009 21:59:30 GMT

I am connecting to a SalesForce DB. Done.

Now, I want to load all fields from all tables:

What is the script that I need to use ?

After that, I want to create two list boxes showing the Tables (1) and the Fields (2) so I can expose which field is associated to which Table.

Who can help me doing that ?

Thanks in advance !

Create list boxes with the list of Tables and associated fields

Oleg_Troyansky — Fri, 08 May 2009 22:07:04 GMT

With the standard database, you would use commands SQLTABLES and SQLCOLUMNS. Try them - I'm not sure Salesforce.com connector supports those commands.

if you don't mind sharing - it would be extremely interesting to see 🙂

Oleg

Create list boxes with the list of Tables and associated fields

Anonymous — Fri, 08 May 2009 22:14:00 GMT

The answer to your first question about what to load from the database, depends on the specific requirements. You certainly don't want load everything...

The second question is easy. When you select fields to show on the sheet, check "Show System Fields", and select $Table and $Field. You'll get exactly these list boxes.
In addition, you can create System Table - it is available from the "New Sheet Object" menu, the last one. And use the table viewer - CTRL+T.

Create list boxes with the list of Tables and associated fields

Fri, 08 May 2009 22:45:25 GMT

sqltables does not work...

As I am really nowhere in QlikView or sql programming, may be you can further help me...

I do have the following script:

CUSTOMCONNECT TO Database, username and password

SELECT

$Table

$Field

FROM ???

What shall the xact script look like ??? Thanks for your help !

Create list boxes with the list of Tables and associated fields

Anonymous — Fri, 08 May 2009 22:53:20 GMT

Jeff,
It's a different question. To read a database table from the database, you use
SQL SELECT
<your database fields here, comma separated>
FROM <a table>
But first of all you need some basic training. You can't create a load script without at least some initial knowledge of SQL. Try using wizard - see "Select" button belo "Connect" in the "Edit Script" window.

Create list boxes with the list of Tables and associated fields

Fri, 08 May 2009 22:59:11 GMT

Ok Tanks, I'll do basic training. I promise !

But i would really appreciate if you can help me... So let's do it this way:

First line connect me to the Salesforce db, it works. My problem is that I'd lihe two list boxes with the fields contained in each table so I can identify which ones I need.

When I SELECT in the load section of the script, I can view one list on the left with the tables contained in my db and if I click on one table, the list of fields of that table.

Now, to reproduce that in QlikView, How do I do ?

CUSTOMECONNECT TO XXXXX

SQL SELECT

$Fields

FROM $table

??? (thanks a lot for your help !)

Create list boxes with the list of Tables and associated fields

Oleg_Troyansky — Fri, 08 May 2009 23:17:11 GMT

Jeff,

Salesforce.com is a "different animal"... we all depend on what features do they support with their connector...

Maybe you can get started with pulling some basic data from specific tables, and not try to build a metadata application right away/

I believe you should be able to use "Select" wizard once you connect to the database - you will see all the tables and fields there.

I honestly don't know how to pull the list of tables and fields from Salesforce.com.

Did anybody out there ever pulled the full list of tables and fields from Salesforce.com ? Please respond!

Oleg

Create list boxes with the list of Tables and associated fields

Fri, 08 May 2009 23:27:13 GMT

Thanks Oleg. In fact, I do not think this is linked to SalesForce.

I would find it logical, when you create a new application, by starting to load the 'tables titles' and related 'fields tables' to get familiar with the DB, without looking at the value !

Let's wait if anyone can answer this.

Cheers !

Create list boxes with the list of Tables and associated fields

Fri, 08 May 2009 23:33:35 GMT

I believe there's a QV file for Salesforce that might be give you some direction.

Regards

Eraaj

Create list boxes with the list of Tables and associated fields

Anonymous — Fri, 08 May 2009 23:37:32 GMT

There is application "QlikView for SalesForce" on http://demo.qlik.com/

Create list boxes with the list of Tables and associated fields

Oleg_Troyansky — Sat, 09 May 2009 00:26:16 GMT


jeffrobertz wrote:I would find it logical, when you create a new application, by starting to load the 'tables titles' and related 'fields tables' to get familiar with the DB, without looking at the value !

Jeff, this sounds very logical, and there are some tools for that:

1. "Select" wizard provides that functionality and is working well for small databases.

2. Most databases and their ODBC drivers support standard sql commands:

SQLTABLES;

SQLCOLUMNS;

However, I must say - in real life we are often connecting to huge ERP systems like SAP and JDE - they have so many tables and fields that it is not practical to learn their structures by browsing their tables... You need to know or ask someone who knows, to identify the dozen or so tables that you actually need.

I hope it makes sense...

Oleg