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
