Joining in Qlikview

[#1]

Hi,

I'm new to qlikview and i know that qlikview  provides automatic association between the tables when the field headers are same  from both the table. I'm trying to create a reports and dashboards for HDFS data by establishing hive ODBC connection and i'm able to connect to hive and query the data and able to create sample report as well.

Here i'm query is suppose if you want to join a 2 tables, Qlikview provides automatic association when the headers are same. Suppose if you want to join 3 tables(or more tables) in a single sheet , just loading the 3 tables will yield the proper result or do we need to manually modify the script?

For example ,if i have a 3 tables like below:

order	customer	product
orderID	customerID	productID
customerID	cust_des	prod_des
productID	address	quantity
dayID

In this case, in my report i wanted to have the fields like orderID,cust_des and prod_des. So just loading these 3 tables give the proper output?

2) My 2nd query is how creating report and dashboard is different in qlikview. Is there any seperate widget to create dashboard/ So how can i create dashboard in qlikview? (steps)

Thanks,

Shree

[Gysbert_Wassenaar]

1. If you load only those three fields from the three tables then Qlikview will create one table with all the records from the three tables.

2. A dashboard is a sheet in Qlikview with some sheet objects on it so that the user can see information and interact with the data, for example by making selections. In Qlikview a report is something that can be printed. The report builder of Qlikview is very limited. But then, the idea is to let users interact with data in such a way that generating paper with lists of numbers is no longer necessary.

[Anonymous]

Hi,

You don't have to do any force join in above scenario. QlikView will automatically join them and provide you expected result.

For creating report and dashboard, there are lots of youtube video, article, best practices available on web. Just google it. Also try exploring QlikView examples which are available in QlikView getting started wizard.

[#4]

Hi,

Thanks for the info provided.

Also as you said i don't have to do any force join in above scenario .But if i select all the 3 tables from hive into qlikview it says the below warning:

"One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog."

So does this lead to any data loss after the join operation ?

Here i'm loading all the columns from all the tables.

[Anonymous]

i would be concerned if I saw this warning. Normally you would want to spend 40-50% of your time on your QV data model and validate data with your source system.

Anyway, you have a couple options:

1) be creative with joins

2) create combined keys and load 3 tables separately. Use combined keys to join them in the model

3) rename columns (remember QV use column names to join tables)

3) force concatenation

Now in your example, it looks like you have a perfect start schema and customer and product tables can be related to order table automatically. In that case you actually has nothing to do and you would NOT get that warning you posted above. Your actual tables must be very different and very likely you have columns with the same names in all 3 tables.

[#6]

Perhaps find the Table Viewer or press CTRL T.

Take a print screen and let us see.

[#7]

       gwassenaar     Jun 24, 2014 6:37 AM  (in response to shree_user)      

"1. If you load only those three fields from the three tables then Qlikview will create one table with all the records from the three tables."

How can it join without having the keyfields?

[Anonymous]

Hi Shree,

You may get data variance if you have loop or synthetic keys in your model. The best option to avoid loop or synthetic key is, rename fields which are not relevant for join or create composite key.

Some links on synthetic key and loop for your reference

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references

Thanks

Satya

[#9]

Below is the table viewer.

Hi,

[sujeetsingh]

You can follow this

Best Practices for Data Modelling