Associating Master Calendar with 3 Fact tables

[elenarelinque]

The problem is that I have 3 tables  ("Datos_Producción","P_INFO_RECHAZOS" and "PARTES")with  different dates. The table "PARTES" is related with the table "Datos_Producción" by the field "FECHA_TURNO_MAQUINA" and the table "P_INFO_RECHAZOS" is connected to the table "Datos_Producción" by the field "ORDEN_DE_FABRICACION" but there is no direct relation between the tables "PARTES" and "P_INFO_RECHAZOS".

"Datos_produccion" is my main table so I'm using the field "Date" in my visualizations. However, in one order of production I can have differents registers of defective material but I cant' sum them because the field "Codigo_defecto" and "Rejection_date" is different and I need this.

The problem is that the date of rejection is not the same as the date of production (the field I use in my visualizations) so I have material rejected in june,for example, that was rejected un July but june is the date of production not rejection. If I use the date of rejection in this particular visualization, the filter indicated doesn't work because those are using the date of production:

I used a canonical calendar to solve this but I have 3 tables so it creates a synthetic key and doesn't show data from the table "PARTES".

[ggijben]

Hi @elenarelinque , 

Can you post some sample data of the 3 fact tables? There are multiple solutions, but the best solution depends on the cardinality between the tables.

[elenarelinque]

Of course, attached is the sample. Here is a little explanation:

• Datos_Producción is a table with the orders of production (field: ORDEN_DE_FABRICACION) extracted from an ERP, with the following fields:

COD_MAQUINA,
DESC_MAQUINA,
MAQUINA_PARTE,
CANTIDAD_ACEPTADA,
M2_ACEPTADOS,
M2_CONPEPTO,
M2_TOTAL,
M2_EXT,
KILOS_ACEPTADOS,
BARRAS_ACEPTADAS,
CODIGO_ARTICULO,
CODIGO_FAMILIA,
FASE_REALIZADA,
FECHA_FABRICACION,
Date("FECHA_FABRICACION",'DD/MM/YYYY')as Fecha_p, /*Date*/
Date("FECHA_FABRICACION",'DD/MM/YYYY')as Date2,
ORDEN_DE_FABRICACION,
FECHA,
HOUR(FECHA_FABRICACION) as HORA,
Time( FECHA_FABRICACION, 'hh:mm' ) as HORA_2,
IF(Hour("FECHA_FABRICACION") >= 7 and Hour("FECHA_FABRICACION") <15,'M',IF(Hour("FECHA_FABRICACION") >= 15 and Hour("FECHA_FABRICACION") <23,'T',IF(Hour("FECHA_FABRICACION") >= 23 or Hour("FECHA_FABRICACION") < 7,'N'))) as TURNO,
Date("FECHA_FABRICACION",'DD/MM/YYYY')&'_'&IF(Hour("FECHA_FABRICACION") >= 7 and Hour("FECHA_FABRICACION") <15,'M',IF(Hour("FECHA_FABRICACION") >= 15 and Hour("FECHA_FABRICACION") <23,'T',IF(Hour("FECHA_FABRICACION") >= 23 or Hour("FECHA_FABRICACION") < 7,'N')))&'_'&"MAQUINA_PARTE" as FECHA_TURNO_MAQUINA
;

• P_INFO_RECHAZO is a table with the orders of production (field: ORDEN_DE_FABRICACION) with defective material (fiel: CANTIDA_RECHAZADA). Also extracted from an ERP, with the following fields:

CODIGO_MAQUINA,
ORDEN_DE_FABRICACION,
FASE,
NUM_LINEA_RUTA,
FECHA_RECHAZO,
Date("FECHA_RECHAZO",'DD/MM/YYYY') as FECHA_RECHAZO_FORMATO,
ORDEN_DE_FABRICACION&'_'&FASE&'_'&CODIGO_DEFECTO&'_'&CANTIDAD_RECHAZADA&'_'&CODIGO_MAQUINA&'_'&FECHA_RECHAZO as ID_RECHAZO,
CODIGO_DEFECTO,
TIPO_MATERIAL,
CANTIDAD_RECHAZADA,
LONGITUD,
KILOS_RECHAZADOS,
OBSERVACIONES,
USUARIO,
CODIGO_TRABAJADOR,
FECHA_CONTROL,
STATUS_LEIDO,
FECHA_REPOSICION,
USUARIO_REPOSICION,
NUM_AGRUPACION,
CODIGO_ORG_CALIDAD,
NUMERO_PARTE_CALIDAD,
NUMERO_DOC_INT,
RESERVADO_ALFA_1,
RESERVADO_ALFA_2,
RESERVADO_ALFA_3,
RESERVADO_ALFA_4,
RESERVADO_ALFA_5,
REASIGNAR,
CODIGO_ARTICULO AS COD_ARTICULO,
REPROCESO;

I can't join properly those two tables because I can have more than one lines of defective material (CANTIDAD_RECHAZADA) because of different types of defects (Field: CODIGO_DEFECTO) for one production order (Filed: ORDEN_DE_FABRICACION). The fields that can be related between those two tables are:

CODIGO_MAQUINA= COD_MAQUINA
ORDEN_DE_FABRICACION=ORDEN_DE_FABRICACION (This is the one i'm using)

COD_ARTICULO=CODIGO_ARTICULO
FASE = FASE_REALIZADA

I can't use the date (FECHA_FABRICACION) because an order produced in 07/11 can be rejected (FECHA_RECHAZO) in 07/15 and 07/20 for example. And a order can be produced in 07/11 and 09/11, for example, the amount is not fulled produced in the same date.

• PARTES is a table with information regarding the staff who have worked in the different shifts, it is extracted from another Database. The fields are:

ID AS ID_PARTE,
/*ID AS ID_PARTE_PRODUCCION,/*NUEVO*/
ID_E,
Date("Día",'DD/MM/YYYY') as "Día", /*Date*/
Turno,
Máquina,
Equipo,
Date("Día",'DD/MM/YYYY')&'_'&Turno&'_'&[Máquina] as FECHA_TURNO_MAQUINA,
Date("Día",'DD/MM/YYYY')&'_'&Turno&'_'&[Máquina]&'_'&Equipo as Fecha_Turno_Maquina_Equipo,
Personas,
"Personas preproceso",
"Horas teoricas",
"Cambio Matriz",
Pruebas,
"Pruebas Ok",
"Matrices OK",
Descargas;

The field FECHA_TURNO_MAQUINA is the one I use to connect this table to the Datos_Producción table. I could also use the date (Día=FECHA_FABRICACION).

• PARADAS there is one final table from the same database than the one above. In this table we have stop hours at the production  in the shifts for the table PARTES:

ID AS ID_PARTE,
"Paradas por avería",
"Paradas Operacionales",
"Paradas programadas/preventivo",
("Paradas Operacionales"+"Paradas por avería"+"Paradas programadas/preventivo") as Paradas_total_ok,
Motivo

Those last two tables are connected by the field ID_PARTE. There is a line in the table PARTES for one date, shift and machine but it could have more than one line in the table PARADAS, because of different types of stops (field: Motivo).

I hope the explanation is good enough, thank you so much for your help. I truly appreciate it.

[rwunderlich]

Take a look at this tutorial to see if it matches your requirement. 

Qlikview Cookbook: Tutorial - Using Common Date Dimensions https://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob

[elenarelinque]

Thank you Rob, in this case I don't have a single key field for all of the tables so I think it needs a different approach.