Linked table built badly or Bad use of Linked Table?

[dena_reavis]

Dear Experts,

I am working on a data model including several tables that have some common fields, only one table having all of them. I have general ledger expense data with account, org code, and transaction date, but no employee ID. Expense reports have all the fields. Headcount data has no account.

I wanted to see if I could use a linked table to link them together but I am not sure if I can because only the Expense Reports data has all four pieces of my Link Key, below.

I used this method for creating the linked table:  http://www.qlikfix.com/2016/09/08/elegant-link-table-script/

(Big thanks to QlikFix Barry Harmsen for posting that. It is truly elegant and I really want to use it!)

The resulting value for the Link_key field in the headcount data would be 123-456-7890|08/01/2016||123456 and I can separate it into each key field using subfield function with no problem. The value for the Link_key field in the expense report data would be 123-456-7890|08/01/2016|65432|123456.

However, the end result data is not associated correctly. I can find the employee ID that I know has an expense report but it isn't associated with account code 65432 in QlikView.

So, could someone please tell me if I can even do this? If this isn't fundamentally flawed logic, I will continue troubleshooting. If it is, I welcome other suggestions to associate all the data where possible. [I have considered concatenating it all together (headcount, expense reports, and GL) but I was concerned that might adversely affect performance.

Thanks in advance!!

[marcus_sommer · Accepted solution]

A linktable-approach is very often not the best choice to build a datamodel because the building of the linktable could be quite complex and expensive (even if you apply the charming performance-solution from Barry) and linktables could grow very large (sometimes much larger as the biggest fact-table in regard to the number of records). Compared with other concepts like star/snowflake-schemes or big single fact-tables is the UI performance usually quite slower.

Beside these general considerations I couldn't say if a linktable-approach is suitable for your case. I think your problem lies in the fact that your key-fields don't match properly. To resolve it I could imagine to check and fill the missing key-fields and/or the values there, for example with creating a mapping-table of Employee_ID and GL_Account from the Expense_Reports Table and using an appropriate applymap() within the Headcount Table.

- Marcus

[dena_reavis]

Marcus,

I  think the linked table is not a good solution for my example. I inherited a QlikView document that used a linked table but only linking the data that was common among all of the tables, meaning the common key was just the Org Code and Transaction Date. This was fine until some new data is added and the linked table doesn't support the "linkage" across all the data (meaning Org + Trans Date + Emp ID + Account). I have always been a fan of concatenated data modeled into a star schema. I have done a lot of that and I find that performance is great. This was the first app I have encountered a linked table and I appreciate your help figuring out what I should do.

Thank you!