Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Leo_Tribaldo
Frequent Visitor

Crear Nuevas Lineas Partiendo de los datos de 2 tablas

‎01-30-2024 03:05 AM

Hola,

 

La TABLA1 = Es una tabla de donde obtengo los paros de una maquina através de su PLC.

Necesito tener los paros por turno y cuando hay un cambio de turno, ya sea de mañana a tarde, de tarde a noche, o de noche a mañana, solo me genera una linea, por lo que necesito partirla y generar los datos.

La TABLA 2: Es la tabla de turno de la maquina.

La TABLA 3: Es un ejemplo del resultado que busco.

Leo_Tribaldo_1-1706612621745.png

 

TABLA 1 = TABLA PAROS AUTOMATICOS INICIAL        
RECURSOFECHA INICIALFECHA FINALEQUIPODESCRIPCION DEL PAROHORA INICOHORA FINNUMERO DE TURNO 
N02DPuen0208/01/202408/01/2024MAÑANAParo por Acumulación de Material6:22:297:44:47024-0000001 
N02DPuen0208/01/202408/01/2024MAÑANAAveria electrica13:30:0014:30:00024-0000001 
N02DPuen0208/01/202408/01/2024TARDEParo por Acumulación de Material15:00:2316:00:25024-0000025 
N02DPuen0208/01/202408/01/2024TARDEParo por Acumulación de Material21:25:0022:05:00024-0000025 
N02DPuen0208/01/202409/01/2024NOCHEParo por Acumulación de Material23:00:000:32:00024-0000032 
N02DPuen0208/01/202409/01/2024NOCHEParo por Acumulación de Material5:45:006:25:00024-0000032 
         
TABLA 2 =TABLA TURNOS 365        
NUMERO DE TURNORECURSOFECHA INICIALFECHA FINALHORA INICOHORA FINEQUIPO  
024-0000001N02DPuen0208/01/202408/01/20246:00:0014:00:00MAÑANA  
024-0000025N02DPuen0208/01/202408/01/202414:00:0022:00:00TARDE  
024-0000032N02DPuen0208/01/202409/01/202422:00:006:00:00NOCHE  
024-0000099N02DPuen0209/01/202409/01/20246:00:0014:00:00MAÑANA  
         
TABLA RESULTADO = TABLA PAROS AUTOMATICOS MODIFICADA        
         
RECURSOFECHA INICIALFECHA FINALEQUIPODESCRIPCION DEL PAROHORA INICOHORA FINNUMERO DE TURNOOBSERVACIONES
N02DPuen0208/01/202408/01/2024MAÑANAParo por Acumulación de Material6:22:297:44:47024-0000001 
N02DPuen0208/01/202408/01/2024MAÑANAAveria electrica13:30:0014:00:00024-0000001 
N02DPuen0208/01/202408/01/2024MAÑANAAveria electrica14:00:0014:30:00024-0000025Linea automatica
N02DPuen0208/01/202408/01/2024TARDEParo por Acumulación de Material15:00:2316:00:25024-0000025 
N02DPuen0208/01/202408/01/2024TARDEParo por Acumulación de Material21:25:0022:00:00024-0000025 
N02DPuen0208/01/202408/01/2024TARDEParo por Acumulación de Material22:00:0022:05:00024-0000032Linea automatica
N02DPuen0208/01/202409/01/2024NOCHEParo por Acumulación de Material23:00:000:32:00024-0000032 
N02DPuen0208/01/202409/01/2024NOCHEParo por Acumulación de Material5:45:006:00:00024-0000032 
N02DPuen0208/01/202409/01/2024NOCHEParo por Acumulación de Material6:00:006:25:00024-0000099Linea automatica

 

Labels:
Message 1 of 4
245 Views
0
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

‎01-30-2024 03:09 PM

You will want to clean up your data first, remove the redundancies and add the missing event index.

lbendlin_0-1706653324283.pnglbendlin_1-1706653359666.png

Then you can create a table visual that has the required columns from both (disconnected) tables. 

lbendlin_2-1706653437962.png

 

 

Lastly you create the measure that calculates the intersect between the events and the shifts.

 

 

 

M HORA INICO = 
var de_inico = min('Shifts'[FECHA INICIAL])+min('Shifts'[HORA INICO])
var de_fin = max('Shifts'[FECHA FINAL])+max('Shifts'[HORA FIN])
var dr_inico = min('Items'[FECHA INICIAL])+min('Items'[HORA INICO])
var dr_fin = max('Items'[FECHA FINAL])+max('Items'[HORA FIN])
var r = intersect(GENERATESERIES(int(de_inico*1440),int(de_fin*1440)),GENERATESERIES(int(dr_inico*1440),int(dr_fin*1440)))
return if(COUNTROWS(r)>0, dt"1899-12-30"+ minx(r,[Value])/1440)

M HORA FIN = 
var de_inico = min('Shifts'[FECHA INICIAL])+min('Shifts'[HORA INICO])
var de_fin = max('Shifts'[FECHA FINAL])+max('Shifts'[HORA FIN])
var dr_inico = min('Items'[FECHA INICIAL])+min('Items'[HORA INICO])
var dr_fin = max('Items'[FECHA FINAL])+max('Items'[HORA FIN])
var r = intersect(GENERATESERIES(int(de_inico*1440),int(de_fin*1440)),GENERATESERIES(int(dr_inico*1440),int(dr_fin*1440)))
return if(COUNTROWS(r)>0, dt"1899-12-30"+ maxx(r,[Value])/1440)

 

 

And this is the result

lbendlin_3-1706656644338.png

Note that item 6 is missing 15 minutes because of a missing shift reference.

 

 

 

 

 

View solution in original post

Incidents.pbix
Message 2 of 4
209 Views
3 REPLIES 3
Leo_Tribaldo
Frequent Visitor

‎01-31-2024 06:25 AM

Perfect, thank you so much.

Message 4 of 4
192 Views
0
lbendlin
Super User
Super User

‎01-30-2024 03:09 PM

You will want to clean up your data first, remove the redundancies and add the missing event index.

lbendlin_0-1706653324283.pnglbendlin_1-1706653359666.png

Then you can create a table visual that has the required columns from both (disconnected) tables. 

lbendlin_2-1706653437962.png

 

 

Lastly you create the measure that calculates the intersect between the events and the shifts.

 

 

 

M HORA INICO = 
var de_inico = min('Shifts'[FECHA INICIAL])+min('Shifts'[HORA INICO])
var de_fin = max('Shifts'[FECHA FINAL])+max('Shifts'[HORA FIN])
var dr_inico = min('Items'[FECHA INICIAL])+min('Items'[HORA INICO])
var dr_fin = max('Items'[FECHA FINAL])+max('Items'[HORA FIN])
var r = intersect(GENERATESERIES(int(de_inico*1440),int(de_fin*1440)),GENERATESERIES(int(dr_inico*1440),int(dr_fin*1440)))
return if(COUNTROWS(r)>0, dt"1899-12-30"+ minx(r,[Value])/1440)

M HORA FIN = 
var de_inico = min('Shifts'[FECHA INICIAL])+min('Shifts'[HORA INICO])
var de_fin = max('Shifts'[FECHA FINAL])+max('Shifts'[HORA FIN])
var dr_inico = min('Items'[FECHA INICIAL])+min('Items'[HORA INICO])
var dr_fin = max('Items'[FECHA FINAL])+max('Items'[HORA FIN])
var r = intersect(GENERATESERIES(int(de_inico*1440),int(de_fin*1440)),GENERATESERIES(int(dr_inico*1440),int(dr_fin*1440)))
return if(COUNTROWS(r)>0, dt"1899-12-30"+ maxx(r,[Value])/1440)

 

 

And this is the result

lbendlin_3-1706656644338.png

Note that item 6 is missing 15 minutes because of a missing shift reference.

 

 

 

 

 

Incidents.pbix
Message 2 of 4
210 Views
Leo_Tribaldo
Frequent Visitor
In response to lbendlin

‎01-31-2024 06:27 AM

Amazing!!!!

Message 3 of 4
188 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All
Top Solution Authors
View All