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

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

mike3451 — Thu, 26 Oct 2017 21:41:31 GMT

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?

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

luismadriz — Fri, 27 Oct 2017 01:19:17 GMT

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

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

mike3451 — Fri, 27 Oct 2017 01:34:43 GMT

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

Michael Venable

Assurance Senior Associate

Phone: 502-882-4592 ._.

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

luismadriz — Fri, 27 Oct 2017 06:01:52 GMT

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

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

jonathandienst — Fri, 27 Oct 2017 06:47:23 GMT

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