topic Need assistance with scripting in QlikView

Need assistance with scripting

Wed, 13 Oct 2010 14:45:14 GMT

Hi,

I have a requirement to bring my SQL database to QlikView, so that users can produce adhoc reports using QlikView. My SQL database has a group of 40 tables. All these tables are related. Few are innter joins and few are outer joins.

When I bring these tables to QlikView as a simple Load statement, I am getting all 40 tables in to QlikView but they are joined based on column names (which is inner join).

But I want to retain the same type of joins that I have in SQL database in QlikView.

Could any one please help me with this?

Regards,

Murali

Need assistance with scripting

dirk_konings — Wed, 13 Oct 2010 14:57:09 GMT

join can define your own joins in script.

TableA:

load * ;

SQL select * from tableA ;

left join (TableA)

Load * ;

SQL select * from tableB ;

The join will be done on same fieldnames. If you don't want this, remane the field(s) in one of the tables.

In result you will get just one table in QV.

If you want to keep the 2 seperate tables, but don't want the records that don't exist in the other table, look at the exists() function.

Need assistance with scripting

Wed, 13 Oct 2010 15:23:03 GMT

Thanks for the quick response Drik Konings.

My doubt is, how can I have 2 separate tables and also I want the records that doesn't exist in the other table.

The issue is I have to do this to all 40 tables so that the QlikView table structure is same as it is in SQL.

Regards,

Murali

Need assistance with scripting

Wed, 13 Oct 2010 15:27:44 GMT

Hi Murali

QlikView's data model is a bit different than a Relational Data Model.

QV joines tables together using same fieldnames. Regardless of datatype, length etc. of the sourcefields. If you have i.e. in every of your tables a field called "ID", QV will join all 40 tables together. So use the aliasing (normally "AS" ) for renaming fields during loads. If you have same fieldname like "name" use cust_name, emp_name, vend_name etc. to make the difference. Another way is to use the Qualify - Statement. This is one (technical) issue.

Another issue: if you want to bring some data together from different tables into one. QV works fine with a model build as a star schema. I prefer data models in my QV-applications with one (even huge and redundant) facts-table and only a few dimension-tables. Don't hesitate to denormalize your model. I avoid sync-tables whereever possible.

So, if your model is not a star schema, try to build one.

Hope this helps for the moment

Roland

Need assistance with scripting

Wed, 13 Oct 2010 15:37:46 GMT

Hi Roland,

Currently I have used the Qualify statement to prefix all coulmns with tables names and alias the column names to create proper joins in QLikview as per the schema definied in SQL.

The only problem I am having now is, the QlikView is making inner join and because of which I am unable to show any data in QlikView. This is because most of the joins in my database are ouet joins.

I also used Concatenate to create a fact table. But still I need outer join feature to retain the non matching records in the QlikView dashboard.

Any help?

Regards,

Murali

Need assistance with scripting

Wed, 13 Oct 2010 15:41:13 GMT

Hi,

I just answered, but i did't know that you have to load it with the same structure it is in your source database. I think for QV itisnt a good idea to do so.

But if you have to, try using the Qualify-Statemant. Unqualify the fields with same names and table that you want to join.

Roland

Need assistance with scripting

dirk_konings — Wed, 13 Oct 2010 15:47:51 GMT

this could be solved with Set Analysis.

sum( {1-$} Sales )
returns sales for everything excluded by the current selection

Need assistance with scripting

Wed, 13 Oct 2010 15:49:53 GMT

Hi Murali,

you can use an outer (left or Right) Join in your Load - Statemant. Look at your Manual or OnlineHelp for the correct syntax. Take care of loading to much data.

Regards

Roland

Need assistance with scripting

Wed, 13 Oct 2010 16:13:16 GMT

Thanks for your Response.

I will proceed as per your advice.