Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JavierBlanco
Contributor III
Contributor III

Dealing with synthetic keys

Hi there:

My client has provided me with some tables in order to create a Dashboard; if I load them into Qlik without further transformation I get this:

Captura1.PNG

$Syn 1 = ID_MES+FCH_ACTO_COMERCIAL
$Syn 2 = ID_SFID+ID_MES
$Syn 3 = ID_SFID+ID_MES+LINEA_NEGOCIO_NMR
$Syn 4 = ID_SFID+ID_MES+FCH_ACTO_COMERCIAL
 
$Syn 5 = $Syn 2+$Syn 3
$Syn 6 = $Syn 1+$Syn 2+$Syn 4
$Syn 7 = $Syn 1+$Syn 2+$Syn 3+$Syn 4
$Syn 8 = $Syn 5+$Syn 6+$Syn 7
 

How bad is this? Cause according to official documentation:

Multiple synthetic keys are often a symptom of an incorrect data model, but not necessarily. However, a sure sign of an incorrect data model is if you have synthetic keys based on other synthetic keys.

I have read here and here that, indeed, there's no need to worry about synthetic keys themselves, but I do have several synthetic combinations ($Syn5 to $Syn8) that seem to be a sure sign of an incorrect data model.

I realize there are some redundancies; for instance, ID_MES is included within FCH_ACTO_COMERCIAL (even ID_ANIO goes within ID_MES, but at least ID_ANIO only appears in one table, TABLA_FCH_DIA, and doesn't mess with connections between tables):

ID_ANIOID_MESFCH_ACTO_COMERCIAL
202020200120200101

 

Anyway, I have tried some test graphics and they work pretty fine:

InkedCaptura3.jpg


Can't say the same when I try to
optimize the data model with my own keys:

Hash256(ID_SFID, ID_MES, FCH_ACTO_COMERCIAL) as %key1
Hash256(ID_MES, ID_SFID, LINEA_NEGOCIO_NMR) as %key2

Captura2.PNG
Synthetics go away and it looks much nicer, but some of my graphs stop working properly:

InkedCaptura4.jpg

For instance, now it seems it's unable to relate TABLA_TRAFICO (where NUM_TRAFICO_BRUTO belongs to) to TABLA_SFID (where DE_NOMBRE_MANAGER_KAM and DE_ENSENIA belong to), and no idea what happens to the forth widget, that disappears completely (all of their fields belong to the same table, ACTIVIDAD_PPALES_LOB). 

Well, thanks in advance for your help.

Labels (1)
0 Replies