Help Remove Syn Table

[#1]

The attached file creates a syn key when I load. My main problem is I have PnL numbers coming from 2 different sources. These sources share a common field called GroupID which is causing the syn table. Here's a picture of the data model:

I've simplified the model to make it easier. The real tables and rule for summing the PnL are fairly complex which is why I want to avoid merging the xSales and ySales tables if possible. Thanks!

[#2]

You could create a composite key value on GroupID and SaleID using eg autonumberhash128 but John Witherspoon posted a really great piece on the forum recently about why synthetic keys are maybe not to be scared of which you might find interesting. The bottom line is that synthetic keys are only mimicking what you would do programmatically anyway so why not leave them alone?

Regards,

Gordon

[#3 · Accepted solution]

Hi Moshe,

Synthetic fields/tables are no big deal unless they slow down the reload time. From your data model, why don't you concatenate xSales & ySales into a massive large Sales table?

You could even add an extra string to each load statement so that you can identify whether they come from xSales and ySales (i.e. 'xSales' as Source in xSales table load and 'ySales' as ySales in table load).

Can you give more reasons as to why you want to get rid of the synkey?

Otherwise, create your own linktable by loading all the SaleID, GroupID and another field which is SaleID & GroupID as Linkfield into a LinkTable. Then on xSales and ySales, rather than load SaleID and GroupID as individual fields, load SaleID & GroupID as Linkfield.

Does this make sense or can you share your script?

[#4]

Thanks for the responses. Honestly, I haven't noticed any problems. Do you think creating one large table would be more efficient? Or using the Linkfield method you described. I don't mind the syn table, I just want to make sure I'm modeling the data in the best way possible. To simplify this example, I even took at a third table that is separate from xSales and ySales that also has GroupID.

[#5]

Hi Moshe,

Like Gordon says, if you have no problems with the SynKey & SynTable, let QV figure out the linking, no need to break your head on that one.

If you want front end objects with an expression that will, for example, relate to ALL the sales values in the xSales & ySales, then you are best off making one big transactional DB table. From the moment QlikView sees 2 tables with exactly the same field names, it automatically concatenates them.

This depends on your front-end specs. What I was suggesting is creating a big table with all the values irrespective of whether they come xSales & ySales.

A typical example of this would be the following. Please say if it makes sense.

Table1:

Load X, Y, Z , A, null() as B, 'Table1' as Source

From Table 1;

Table 2:

Load X, Y Z, null() as A, B, 'Table2' as Source

From Table 2;

In the above statement, Tables 1 & 2 share fields X, Y and Z, but field A is unique to Table1 and field B is unique to Table2.

If I then want to do an aggregation, for example sum(Z), then this will include ALL values from Table1 and Table2. I then can use Source in set analysis or as a selection Listbox to look at data that only comes from Table1 or Table2.

Hope this makes sense.

[#6]

Thanks for the responses. I think I'm going to stick with the syn table for now. I've been testing and there haven't been any problems. If there are any, I'll probably take the approach of combining the tables. I want to avoid this, however, as I would have to change the set analysis in hundreds of equations. Thanks again.