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.
