Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mike3451
Contributor
Contributor

Need Help - Analyzing data using non-common data fields across fact tables linked by common fields with a LINK TABLE

Successfully loaded the various fact tables and generated link-table in load script.  The issue I am having is aggregating or summing across fact tables.  In my current data model i have the following fact tables:

Vendor Master

Vendor Address

Invoice

Requisition

PO LIne Item

PO Master

Disbursements

Receiving

Employee Master

Various fields are shared across the tables but no one field or fields are shared across all.  I generated a link table to account for this (see attached for link table script). 

I am having issues summarizing across fact tables based on on non-common data fields. 

For example:  Using Vendor Name from the Vendor Master File as the dimension calculate the total dollar values of payments, and invoices, requisition, and purchase orders.   The Vendor ID is foreign key or common field across the Vendor Master File, Invoice, Requisition, and Purchase Order tables and the PO Number is the foreign key between the PO table and the Invoice table.

With the Vendor Name field in the Vendor Master Table I believe i should be able to calculate this.  However, when i do so it just says all values are null.  However, when i use VendorID it works.  Is there something i am missing or should I be using INDIRECT SET ANALYSIS.  Any help or guidance would be very much appreciated.  The data sets are on the larger side 400 GB in total.  Could this be causing the error?

4 Replies
luismadriz
Specialist
Specialist

Hi Michael, can you please share a extract of the data so I can run that loading script and see the data and the model?

Cheers,

Luis

mike3451
Contributor
Contributor
Author

I wish I could, unfortunately I am unable to due to security and privacy laws.

Michael Venable

Assurance Senior Associate

Phone: 502-882-4592 ._.

luismadriz
Specialist
Specialist

Ok no worries. Although remember that you could disguise a fraction of it as long as the data is still technically relevant. I hope you understand that without it is difficult to help, well at least for me,

Regards,

Luis

jonathandienst
Partner - Champion III
Partner - Champion III

This sort of analysis is better served by a single concatenated fact table. Your data set is quite large, so performance optimisation needs to be considered.

With a single fact table

  • The data model can be a simple star or snowflake schema
  • It will generally perform much better than a complex model with large link tables
  • You will not get confusion over how selections and aggregations are behaving
  • You don't need to build a complex link table which could be very time consuming

Qlikview is not SQL - the concatenated facts do not have to have the same set of fields, and even quite disparate information can be represented in a single fact table as long as there are some common dimensions between them.

Get started with developing qlik datamodels

Advanced topics for creating a qlik datamodel

More advanced topics of qlik datamodels

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein