Assistance with removing synthetic keys

[Ahmed_Turnaround]

Hi,

I'm a complete novice at this, if it wasn't obvious and I've done some research and found that having synthetic keys are frowned upon and should be removed. 

I just wanted some advice on how you would do this task. In this screenshot, you will my tables that I am using in my QlikView Application. You will also see that $Syn1 has been created by QlikView on fields ITEM ID and CUSTOMER ID which are common in both TA.SalesHistory and TA.Backorders. 

TA.Backorders doesn't link to TA.SalesHistory in any way but it does have common field names. 

TA.Backorders can link to TA.Customer on field CUSTOMER ID and TA.Item on field ITEM ID. 

TA.SalesHistory can link to TA.Customer on field CUSTOMER ID and TA.Item on field ITEM ID. 

So how would you go about removing this synthetic key? I don't really want TA.SalesHistory and TA.Backorder to link together, which I gather is what $Syn is doing? 

Thank you for any help.

[martinpohl · Accepted solution]

what is $Sync doing?

Qlik creates a Sync table with all possible combination of CUSTOMER ID and ITEM ID that are in your values.

How to avoid it?

Possible 1: Create a field CUSTOMER ID & '-' & ITEM ID in both tables plus a Link field table 

CUSTOMER ID & ITEM ID and also CUSTOMER ID and ITEM ID for selections an relationship to customer and item table

Possible 2: Concatenate load your datas. All fields within the same name (CUSTOMER ID and ITEM ID, maybe other fields) are in the same field, different fields (Document cumber and sales rep) are in theses line they are belogns to.

I would prefer possible 2 and create as many same named fields as possible (document no and order no as number, sales rep into same field and so on but value field (order quantity and sales quantity) into diffenrent fields.

Regards

[Ahmed_Turnaround]

Hi @martinpohl

Thanks for your reply. 

I tried the first method by creating a field for Customer ID & Item ID but it created problems where other data would go missing. 

How would I go about doing the second method? 

I'm very new to all of this. 

[martinpohl]

Facts:

load

"CUSTOMER ID",

"ITEM ID",

"SALES PROCES NUMBER" as NUMBER,

"OUTSTANDING QUANTITY"

from source1;

concatenate (Facts) load

"CUSTOMER ID",

"ITEM ID",

"SALES NUMBER" as NUMBER,

"SALES QUANTITY",

SALESREP

from source2;

Regards

[Ahmed_Turnaround]

Hi @martinpohl 

Thanks for your reply. From what you are telling me to do, does that mean it will link Backorders to Sales History? Neither are linked and shouldn't be linked. I just need to remove the synthetic key 

[martinpohl]

no, they are not linked, they are in the same table.

Some fields are filled with values from both table, some fields are only filled from one or the other table but you can select all values (eg CUSTOMER).

Try it

Regards

[Ahmed_Turnaround]

Hi @martinpohl,

Thanks for your reply. I will try that now and report back ASAP. 

[Ahmed_Turnaround]

Hi @martinpohl ,

Where are you seeing the fields 

SALES QUANTITY and SALESREP, I can't see them in the QlikView fields and therefore I'm getting this error:

Should I be using QUANTITY from the TA.SalesHistory for Sales Quantity and SALES REP CODE from TA.SalesHistory?

[martinpohl]

sure, use YOUR fields but try to use as much same fields from both tables as possible

Regards

[Ahmed_Turnaround]

Hi @martinpohl ,

The problem is, there is no Sales Number on the TA.SalesHistory file. The only common fields really are CUSTOMER ID and ITEM ID. Possibly SALES REP CODE. 

I don't understand how your solution will work?