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: 
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
johnw
Champion III
Champion III

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.

View solution in original post

8 Replies
Not applicable
Author

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

Not applicable
Author

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.

johnw
Champion III
Champion III

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
Author

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
Author

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

johnw
Champion III
Champion III

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
Author

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

Gavin.

Not applicable
Author

Why not use CONCATENATE?