Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !!!
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.
There is a thread for the same problem http://community.qlik.com/forums/p/31028/120041.aspx#120041
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.
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.
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
You could do a LEFT JOIN LOAD and store them into a single qvd?
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.
Thank you for all your thoughts .....
Gavin.
Why not use CONCATENATE?