topic Re: Assistance with removing synthetic keys in QlikView

Assistance with removing synthetic keys

Ahmed_Turnaround — Thu, 16 Jan 2020 14:26:22 GMT

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.

Re: Assistance with removing synthetic keys

martinpohl — Thu, 16 Jan 2020 14:36:43 GMT

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

Re: Assistance with removing synthetic keys

Ahmed_Turnaround — Thu, 16 Jan 2020 14:38:32 GMT

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.

Re: Assistance with removing synthetic keys

martinpohl — Thu, 16 Jan 2020 14:42:47 GMT

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

Re: Assistance with removing synthetic keys

Ahmed_Turnaround — Thu, 16 Jan 2020 15:00:41 GMT

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

Re: Assistance with removing synthetic keys

martinpohl — Thu, 16 Jan 2020 15:04:52 GMT

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

Re: Assistance with removing synthetic keys

Ahmed_Turnaround — Thu, 16 Jan 2020 15:06:40 GMT

Hi @martinpohl,

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

Re: Assistance with removing synthetic keys

Ahmed_Turnaround — Thu, 16 Jan 2020 15:26:48 GMT

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?

Re: Assistance with removing synthetic keys

martinpohl — Thu, 16 Jan 2020 15:30:36 GMT

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

Regards

Re: Assistance with removing synthetic keys

Ahmed_Turnaround — Thu, 16 Jan 2020 15:33:03 GMT

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?