When synth key is a solution, how do i do ?

[employe4_2]

Hi i'm in a situation where i got a synth key and  don't know how to handel it.

Here is the situation: to make bottles of water we use the process of blowmolding. First step there is extrusion: plastic is melted and pushed into a mold, Second step, we blow into the mold so the plastic take the shape of a bottle.

My first table concern the extrusion part and my second the molding part, so in each table there is the date (unixdatetime)the number of identification (  for each bottles produced) and a measure ( temp for extrusion, and pressur for blowing).

My probleme is, that i need to display information coming from both tables function of time or Identification number so if i link tables with only one field,  i multiply *2 the other field wich make some charts impossible to display ( exemple: if number of identification link the tables, i have 2 fields for date ( date.mold and date.extr) wich is pretty annoying when i want to display temp and pressur function of time. Same for time, if time link the tables, i have two fields for number of identification which correspond to one product, so it has no sense and wont let me scatter plot (dim: iD; measur1:temp;measur2:pressur)))

I tried to concatenate the two table but it also duplicate some data

I can elaborate my probleme if someone have some ideas/questions

thx

[borismichel]

Hi Employe4_2,

if I understand you correctly, you want to know how to form a link between two tables using two keyfields without creating a synthetic key?

If so, the most common way is to create a compound field by concatenating field values to create a new field which will act as a key. In your case that would be something like:

Extrusion:
LOAD
date as extrusion.date,
id as extrusion.id,
date & '_' & id as key_date_id,
...

Molding:
LOAD
date as molding.date,
id as molding.id,
date & '_' & id as key_date_id,
....

The problem is, that this will only link the two tables for every existing pairing of id and date in BOTH tables. Which is a problem since you have to decide which date (extr or mold) should be your dimension. You can create a link table containing all dates and ids by concatenating these fields from both tables and generate the key accordingly. The id and date from this link table could then be used as dimensions.

Also: String concatenations lead to string keys which can be slow depending on the amount of data. If you pack the concatenation in an autonumber function, your keys will be integers:

Molding:
LOAD
date as molding.date,
id as molding.id,
AutoNumber(date & '_' & id) as key_date_id,
....

 
You can use the data manager as well to create custom key fields, see the Compound Key Section of the following help article:
https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/LoadData/managing-as...

Hope this helps

Boris