Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Linking large tables – Synthetic Key, Join or Manual Key ?

Hello all,

I have 2 tables that are populated by multiple spread sheets over night and are then stored in QVD files for applications to access. One table grows by around 200,000 a night and the other by about 1,000,000. Since we are just starting out with this I would like to get the structure correct from the outset....

Table X:

A,B,C,D,E

Table Y,

A,B,1,2,3,4 ......26

Coming from a relational database background I am simply loading the 2 QVD into my application and let QlikView create a synthetic table to join them.

My charts/tables will contain a mixture of information from the both tables.

Is this a efficient way to handle this amount of data ?

Should I use a join each night to create one table and save this as a QVD ?

Should I create a manual link between the 2 tables ?

Is this infact the best way to do this ?

As the information will only be loaded out of hours I would prefer the overhead to be happening here if possible.

Thanks for the help !!!

1 Solution

Accepted Solutions
MVP
MVP

Linking large tables – Synthetic Key, Join or Manual Key ?

I probably wouldn't left join into a single table, as that would duplicate the header information across all the rows. That MIGHT not be a problem, such as if the header information just stores some things like status and customer and the like. But if it stores, say, an account balance, you wouldn't want to duplicate that balance across all the line items. Same with pretty much any other numerical information. And even if there isn't any numerical information currently stored on the header, it would not be unreasonable to add some later, and you wouldn't want to have to fix the data model down the road. So I'd probably just leave them as two separate tables connected through a synthetic key.

8 Replies
Not applicable

Linking large tables – Synthetic Key, Join or Manual Key ?

There is a thread for the same problem http://community.qlik.com/forums/p/31028/120041.aspx#120041

Not applicable

Linking large tables – Synthetic Key, Join or Manual Key ?

Nick,

Thanks for the reply, I have been following the thread that you mentioned, in fact it was that thread that made me think of this. From my understanding that thread is discussing should you use synthetic keys as all, I thought my question was more along the lines of how best to structure your data module therefore did not post it in that thread.

Thanks Gavin.

MVP
MVP

Linking large tables – Synthetic Key, Join or Manual Key ?

I would probably only join them into one table if they are logically the "same" table - both uniquely keyed by A & B and nothing else. That you have 5x as much data in Table Y, however, leads me to believe that is likely not the case. For instance, table X might indeed be uniquely keyed by A & B, but table Y is only uniquely keyed by A & B & 1. I wouldn't join them then, because it would duplicate the Table X data across the rows of Table Y, which would likely mess things up.

So I'd probably just read them in, let QlikView build the synthetic key, and call it good.

It's hard to know without knowing WHAT the data is, though.

Not applicable

Linking large tables – Synthetic Key, Join or Manual Key ?

Thanks for the rely,

Table A is in fact a header table for Table B. Table A contains the transaction header information and table B contains all the detail lines. They are both unique by store number, transaction ID

Not applicable

Linking large tables – Synthetic Key, Join or Manual Key ?

You could do a LEFT JOIN LOAD and store them into a single qvd?

MVP
MVP

Linking large tables – Synthetic Key, Join or Manual Key ?

I probably wouldn't left join into a single table, as that would duplicate the header information across all the rows. That MIGHT not be a problem, such as if the header information just stores some things like status and customer and the like. But if it stores, say, an account balance, you wouldn't want to duplicate that balance across all the line items. Same with pretty much any other numerical information. And even if there isn't any numerical information currently stored on the header, it would not be unreasonable to add some later, and you wouldn't want to have to fix the data model down the road. So I'd probably just leave them as two separate tables connected through a synthetic key.

Not applicable

Linking large tables – Synthetic Key, Join or Manual Key ?

Thank you for all your thoughts ..... Yes

Gavin.

Not applicable

Linking large tables – Synthetic Key, Join or Manual Key ?

Why not use CONCATENATE?

Community Browser