Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
HamdiGA
Contributor II
Contributor II

Create Facts Table

Hello, 
I'am new to qlik sense. I want to create my facts table from 2 dimensions here is my exemple : 
1) first i load the 2 dimensions from the data base [ VM, Histo_VM]

2) i create a fact table based on sql querys on the two dimensions :

[VM]:
LOAD
[ID_VM],
[ID_MISSION] AS [VM.ID_MISSION],
[ID_MISSION_VM] AS [VM.ID_MISSION_VM],
[NUM_SEQUENCE],
[ETAT] AS [VM.ETAT],
[TYPE_ALLEE],
[TYPE_POSTE],
[ID_POSTE],
[ZONE],
[NIVEAU] AS [VM.NIVEAU],
[ALLEE],
[COLONNE],
[INDICE],
[ID_PALETTE] AS [VM.ID_PALETTE],
[BATTERIE],
[LIEU_BATT],
[TEMPS_BATT];
SELECT "ID_VM",
"ID_MISSION",
"ID_MISSION_VM",
"NUM_SEQUENCE",
"ETAT",
"TYPE_ALLEE",
"TYPE_POSTE",
"ID_POSTE",
"ZONE",
"NIVEAU",
"ALLEE",
"COLONNE",
"INDICE",
"ID_PALETTE",
"BATTERIE",
"LIEU_BATT",
"TEMPS_BATT"
FROM "$(dbTableOwner)"."VM";

[HISTO_ETAT_VM_SECU]:
LOAD
[ID_VM] ,
[ANCIEN_STATUT] ,
[NOUVEAU_STATUT] ,
[EN_AUTO] ,
[COM_MITSU] ,
[DMD_VALIDE],
[VALIDE],
[FIGE] ,
[DEBUT_CHGT] ,
[DUREE],
[DATE_MODIF];
SELECT "ID_VM",
"ANCIEN_STATUT",
"NOUVEAU_STATUT",
"EN_AUTO",
"COM_MITSU",
"DMD_VALIDE",
"VALIDE",
"FIGE",
"DEBUT_CHGT",
"DUREE",
"DATE_MODIF"
FROM "$(dbTableOwner)"."HISTO_ETAT_VM_SECU";


/******************** FACTS *********************************************************************************************************************************/
KPI_VM:
sql select vm.id_vm, h.ancien_statut as NB_STOPS_PER_VM,h.date_modif
from histo_etat_vm_secu h
inner join vm on h.id_vm = vm.id_vm
where h.nouveau_statut in (8,0) and h.ancien_statut not in (0,8);
Join
//temps_arret_vm:
sql select id_vm,duree as downtime_vm,h.date_modif
from histo_etat_vm_secu h
where nouveau_statut in (0,8) and ancien_statut in (2,0,8);

join
//nb_figeage_VM:
sql select id_vm, ancien_statut as NB_FIGE_PER_VM,h.date_modif
from histo_etat_vm_secu h
where nouveau_statut in (4) and ancien_statut not in (0,8);

join
//temps_figeage_VM:
sql select id_vm,duree as time_fige_vm,h.date_modif
from histo_etat_vm_secu h
where nouveau_statut in (2,4) and ancien_statut in (2,4);

Is This the validate way to create facts table ? 
Thanks

Labels (3)
0 Replies