Unintentional creation of synthetic keys during data load in QLIK SENSE

[cepp0]

Hello,

I am trying to load multiple tables into QLIKSENSE. However, during the loading process, it appears that a synthetic key is created unintentionally. The tables I am trying to load include Prospects, Budgets, Clienti, CentriDiCosto, CommesseConsultingPerHolding, Commesse, and Facts, and the synthetic key is formed with the fields ProspectId, BudgetId, ClienteId, CDCId, CommessaId and some others.

I attempted to create a LinkTable to solve this synthetic key issue but to no avail. Despite using OrderId, AgentId, and CustomerId for my LinkTable, I have been unable to correctly link it with the Facts table.

Below is the code for each table:

[Prospects]:

LOAD *;

SQL select

prospects.Id as ProspectId,

ProspectName

from

ControlloDiGestione.dbo.T_CDG_BDG_PROSPECTS prospects;

[Budgets]:

LOAD *;

SQL select

Id as BudgetId,

Stato

from

ControlloDiGestione.dbo.T_CDG_BDG_BUDGETS;

[Clienti]:

LOAD *;

SQL select

CardCode as ClienteId,

CardName as Cliente

from

$(vSBOServerName).omicron_pro.dbo.ocrd;

[CentriDiCosto]:

LOAD *;

SQL SELECT

PrcCode as CDCId,

PrcName as CentroDiCosto

FROM

$(vSBOServerName).omicron_pro.dbo.oprc;

[CommesseConsultingPerHolding]:

Mapping Load

CommessaId,

    True() as CommessaHolding;

SQL SELECT

U_PrjCode_A as CommessaId

FROM

$(vSBOServerName).holding_pro.dbo.[@RS_REGCOM];

[Commesse]:

LOAD 

*,

Date(DataApertura) as DataAperturaQlik,

Date(DataChiusura) as DataChiusuraQlik,

    ApplyMap('CommesseConsultingPerHolding', CommessaId, False()) as CommessaHolding;

sql select

  oprj.PrjCode as CommessaId,

  oprj.PrjName as Commessa,

  oprj.U_RS_Tipo as TipoCommessaId,

  oprj.U_RS_SLine as CategoriaId,

  oprj.U_RS_DataAtt as DataApertura,

  oprj.U_RS_DataChi as DataChiusura,

  oprj.U_RS_SlpCode as AgenteId,

  resp.EmpID as ResponsabileId,

  respeff.EmpId as ResponsabileEffettivoId,

  case when costipm.PrjCode is not null then 1 else 0 end as CostoPM, -- ex campo PMCheck

  upper(oprj.U_RS_CardCode) as ClienteId,

  case when

  commesse_da_escludere.CommessaId is not null then 'Y'

    else 'N'

  end as CommessaDaEscludere  

from

  $(vSBOServerName).omicron_pro.dbo.oprj oprj

left join

  VW_CDG_RESPCOMM_EFFETTIVI respeff

on

  oprj.PrjCode = respeff.U_PrjCode

left join 

  (SELECT

    MIN(U_empID) AS EmpID,

    min(U_Resp) AS U_Resp,

    U_PrjCode

  FROM

    $(vSBOServerName).omicron_pro.dbo.[@rs_respcomm]

  WHERE

    U_RS_RespPrinc = 'Y'

  GROUP BY

    U_PrjCode

  ) as resp

on

  oprj.PrjCode = resp.U_PrjCode

left join

  (

    select distinct

      PrjCode

    from

      T_CDG_DATI_REL

    where

      Descr = 'Costo PM'

  ) as costipm

on

  oprj.PrjCode = costipm.PrjCode

left join

  (

    SELECT

      CommessaId

    FROM

      T_CDG_COMMESSE_DA_ESCLUDERE

  ) as commesse_da_escludere

on

  oprj.PrjCode = commesse_da_escludere.CommessaId

where

  oprj.PrjCode <> '-';

// Necessario per eliminare la parte oraria dal campo

drop field DataChiusura from Commesse;

drop field DataApertura from Commesse;

rename field DataAperturaQlik to DataApertura;

rename field DataChiusuraQlik to DataChiusura;

//Creazione del campo DescrizioneCommessa nella tabella Commesse

NoConcatenate

CommesseDescrizione1:

LOAD

*,

Replace(Commessa, CommessaId , '') as DescrizioneCommessa1

RESIDENT

Commesse;

DROP TABLE Commesse;

NoConcatenate

CommesseDescrizione:

LOAD

*,

if(IsNull(DescrizioneCommessa1), CommessaId, DescrizioneCommessa1) as DescrizioneCommessa

Resident

CommesseDescrizione1;

DROP FIELD DescrizioneCommessa1 FROM CommesseDescrizione;

DROP TABLE CommesseDescrizione1;

RENAME TABLE CommesseDescrizione TO Commesse;

[Facts]:

LOAD *;

SQL

select 

  NULL as BPId,

  PrcCode as CDCId,

  COALESCE(oslp_bdg.SlpCode, oslp_prospects.SlpCode) as AgenteId, -- seleziona SlpCode da uno dei due alias di tabella

  NULL as ArticoloId,

  NULL as ContoId,

  'Budget' as ClasseDocId,

  NULL as CommessaId,

  datefromparts(Anno, 1, 1) as DataCompetenza,

  NULL as Descrizione,

  BudgetId,

  ocrd.Cardcode as ClienteId, 

  ProspectId,

  AreaCompetenzaId,

  TipoSrvPrdId,

  NULL as RisorsaId,

  NULL as SocietaId,

  NULL as TipoConsuntivoId,

  'Budget' as TipoDocumentoId,

  Versione as VersioneId,

  Budget,

  Confidenza,

  0 as CostoHStd,

  0 as Consuntivo,

  0 as MOLAtteso,

  0 as MOLAttesoCosti,

  0 as Quantita,

  NULL as Escludi,

  righebdg.Note

FROM

  T_CDG_BDG_RIGHE_BUDGET righebdg

LEFT JOIN

  T_CDG_BDG_BUDGETS bdg

ON 

  righebdg.BudgetId = bdg.Id

LEFT JOIN

  T_CDG_OSLP oslp_bdg

ON 

  bdg.AgenteId = oslp_bdg.Id

LEFT JOIN

  T_CDG_BDG_PROSPECTS prospects

ON 

  righebdg.ProspectId = prospects.Id

LEFT JOIN

  T_CDG_OSLP oslp_prospects

ON 

  prospects.AgenteId = oslp_prospects.Id

LEFT JOIN

  T_CDG_OCRD ocrd

ON

  righebdg.ClienteId = ocrd.Id  

JOIN

  T_CDG_OPRC oprc

ON

  righebdg.CDCId = oprc.Id;

Thank you in advance for any help or insight.

[rubenmarin1]

Hi, you can create a composite key in Facts and Commesse tables

ClienteId &'_'& CommessaId &'_'& AgenteId as LinkKey

And keep those fields only in one of the tables