topic Keeping the table structure in SQL Join in QlikView

Keeping the table structure in SQL Join

Tue, 28 Jul 2015 20:03:27 GMT

Hello,

i could never find an answer to this:

If I use an SQL Select statement with joins QlikView creates only one table for all the selected fields.

Is there any way to keep the multi table structure from my SQL server in QlikView ?

Thanks and Greetings

Hans

Re: Keeping the table structure in SQL Join

maxgro — Tue, 28 Jul 2015 20:31:42 GMT

don't join in SQL

makes 2 load in qlik, each one from a sql table

T1:

load *;

SQL select * from table1;

T2:

load *;

SQL select * from table2;

Re: Keeping the table structure in SQL Join

Wed, 29 Jul 2015 08:05:38 GMT

Thanks, but will the data still be related (joined) ?

For example in the Movies database FilmCountryID and CountryID need to be joined. From the names I don't see how QlikView could join both IDs.

So the data would be useless. Do I first have to rename all related columns in SQL ? That would be inconvenient.

Re: Keeping the table structure in SQL Join

Wed, 29 Jul 2015 08:27:07 GMT

Rename the fields during the QV load statement

Load

CountryID As NewFieldname

;

Sql select........

you need the two ID's to match in your end application for QV to join them

Joe

Re: Keeping the table structure in SQL Join

jonathandienst — Wed, 29 Jul 2015 08:28:16 GMT

The QV data model works very differently than a SQL database and you will often have a data model that is differently structured than the source data.

The way an associative data model works is that associations are made by using the same field name in both tables. If there are multiple fields in common, then QV will create a composite key (aka synthetic key). So yes, you may need to alias the column names during loading to get related fields to associate and prevent unrelated fields with the same name from associating. No need to change the names in SQL.

HTH

Re: Keeping the table structure in SQL Join

Wed, 29 Jul 2015 08:36:41 GMT

Hey Hans Lauer,

I understand your problem, Even I have faced the same issue.

But QV Data model works differently, it does the join based on the column names.

So in order to have both the tables in your qlikview document, you should load it twice and rename the fields in either one of the table.

Thanks

Ganesh

Re: Keeping the table structure in SQL Join

maxgro — Wed, 29 Jul 2015 16:35:40 GMT

yes you have to rename the field (or fields, depends) to associate the 2 tables

Re: Keeping the table structure in SQL Join

Wed, 29 Jul 2015 17:46:24 GMT

Thanks for all the answers, the solution was quite obvious, I somehow made things unnecessarily complicated...

This also explains why the "select" button for SQL tables only allows one table at a time.