topic Re: How to create a table box from database tables in QlikView

How to create a table box from database tables

Thu, 23 May 2013 18:04:34 GMT

Hi,

I want to create a table box so that I can then send the data to excel easily, the problem is that I have data coming from different tables via SQL SELECTS and I want to LOAD the data so that I can create an internal table as the source of the table box.

The format of the data is roughly like this, each columns contents are the results of different queries.

I've tried to create a table using the fields already in the LOAD script and get a cartesian product with 000s of rows, and no blank data.

Any ideas on how to achieve this? I want to have spaces where there's no data e.g. Col1 has 1 row, Col2 has 1, Col3 has 4 rows.

I guess I want 5 list boxes linked together so I can then send the data to Excel.

Comments/ideas?

Thanks

Gary

Re: How to create a table box from database tables

Gysbert_Wassenaar — Thu, 23 May 2013 18:15:37 GMT

You could add a rowid field to every table:

TableA:

load recno() as rowid, Col1

from ...somewhere....

All the tables will be associated on the common rowid field. That prevents the cartesian product.

But why use qlikview anyway. It's probably totally useless in qlikview if you load it that way. You might as well load the data directly from the database into excel.

Re: How to create a table box from database tables

Fri, 24 May 2013 11:40:41 GMT

Hi,

Thanks, I've implemented via the use of recno(), as per below....even thrown in an INLINE too

It works well enough for my purpose;

cntry_tbl:

Load * Inline

[

ROWNO,COUNTRY , COUNTRY_2CHAR

1,$(COUNTRY), $(CNT_2CHAR)

];

temp_table1:

LOAD recno() as ROWNO , JOBCODE_DESC RESIDENT JOBDESC_TBL;

OUTER JOIN

LOAD recno() as ROWNO , VALUE as COMPANY_CD RESIDENT COMPCD_PAY_TBL WHERE COL = 'COMPANY_CD';

OUTER JOIN

LOAD recno() as ROWNO , VALUE as PAYGROUP RESIDENT COMPCD_PAY_TBL WHERE COL = 'PAYGROUP';

OUTER JOIN

LOAD * RESIDENT cntry_tbl;

I agree with your point about why use Qlikview, the reasons are:

I want to gain expertize with QV, especially scripting
I also want to evangelicize the use of QV in our very large organization
I've not had any experience with SQL queries and Excel, with my Oracle database background then I hadn't even considered it.
Eventually I hope to be able to just sent out the .qvw or .qvo for the users to run themselves.

Regards

Gary