Sum of Productive Runtime of Maschines

[KarimBen]

Hello, 
i need your help please. 
We have several Maschines in our Production Line. 
We want to analyse the productivity of the Maschines. 

The Maschine could different states and we want to sum the Time between the different States. 
For example State "Run" at 01:00:00 till State "fault" 01:30:00  that mean 0,5 h or 00:30:00 min productive. 
Attached you will my Script and a example Data set. 

I Work in Qliksense

@sunny_talwar 
@swuehl 

[Taoufiq_Zarra]

@KarimBen  can you elaborate ?

from your sample :

can you share the expected output ?

[KarimBen]

Hello Taoufiq , 

thanks for replying. 
I want the sum of the individual status changes. 

Thats it. 🙂

[KarimBen]

I want to Know how many time was the maschine "läuft" or "nicht produktiv" etc. 
I have Time Stamps Field Time and know i have to sum the Time between the changes of "Indicom". 

[Taoufiq_Zarra · Accepted solution]

@KarimBen  if I undersood correctly, you can use for example in script :

LOAD
    rowid as %rowid,
    date(left("timestamp",10)) as Datum ,
    mid("timestamp",12,8) as Zeit,
    "timestamp",
    tier1 as Grün,
    tier2 as Gelb,
    tier3 as Rot,
    if(tier1=0 and tier2=0 and tier3=0,1) as Counter,
    
    if(tier1=1,'läuft',
    if(tier1=0 and tier2=0 and tier3=0,'nicht produktiv',
    if(tier1=2, 'Schacht i.O. voll',
    if(tier2=1, 'Einrichten',
    if(tier2=2, 'Schacht nicht erfasst voll',
    if(tier3=1,  'Störung',
    if(tier3=2,  'Schacht n.i.O. voll'
    
    ))))))) as IndiCom,    
    "slaveref/refid",
    if("slaveref/macid"= '00A391','Brune4',
    if("slaveref/macid"= '00A399','Brune7')) as Slave,
    %Key_data_CD43325B85172CA2
    

FROM C:\Users\admin\Downloads\MDE_Export.xml
(XmlSimple, table is [data/slavestate])

;
LOAD
    rowid as %ROWID,
    state
FROM C:\Users\admin\Downloads\MDE_Export.xml
(XmlSimple, table is [data/sync])
where state= 'completed'
;

left join ([data/slavestate])
Load
%rowid,
if(IndiCom='läuft', mid("timestamp",12,8), null()) as ClockIn, 
if(IndiCom<>'läuft', mid("timestamp",12,8), null()) as ClockOut,
Datum&'_'&Slave as %Datum%Slave
// ,
// Interval( Time#(ClockOut,'hh:mm:ss TT')-Time#(ClockIn,'hh:mm:ss TT') ,'h') as RunT

Resident [data/slavestate];


left keep  ([data/slavestate])
Load 

//If(IndiCom='läuft',round((ClockOut-ClockIn)*1440,0.02,0 )) as ProdTime
sum((ClockOut -ClockIn))as RunTimeProd,
sum(Date(((ClockOut) - (ClockIn)),'hh:mm:ss')) as RunTimeProdFormat


Resident [data/slavestate]
group by %Datum%Slave, Slave, Datum
;

Data:
noconcatenate

load *,peek(IndiCom) as maschineStatus ,Time(Timestamp(Timestamp#(left(purgechar(timestamp,'T'),18),'YYYY-MM-DDhh:mm:ss'))-Timestamp(Timestamp#(left(purgechar(peek(timestamp),'T'),18),'YYYY-MM-DDhh:mm:ss'))) as maschineTime resident  [data/slavestate];

drop table [data/slavestate];

and the in interface you can show this able :

or you can create this table :

[MarcoWedel · Accepted solution]

Hallo,

another solution might be to resident load the machine runtime table sorted by slave and timestamp descending and calculate the duration of each machine state as difference between the current and previous timestamps.

This way you don't have to aggregate on script level and might be more flexible in the analysis.

tabMaschinenLaufzeitenTemp:
LOAD rowid as %rowid,
     Date(Left(timestamp,10))	as Datum ,
     Mid(timestamp,12,8)		as Zeit,
     Timestamp#(Replace(Left(timestamp,26),'T',' '),'YYYY-MM-DD hh:mm:ss.fffffff') as DatumZeit,
     timestamp,
     tier1 						as Grün,
     tier2						as Gelb,
     tier3						as Rot,
     If(tier1=0 and tier2=0 and tier3=0,1) as Counter,
    
     If( tier1=1, 'läuft',
      If(tier1=0 and tier2=0 and tier3=0,'nicht produktiv',
      If(tier1=2, 'Schacht i.O. voll',
      If(tier2=1, 'Einrichten',
      If(tier2=2, 'Schacht nicht erfasst voll',
      If(tier3=1, 'Störung',
      If(tier3=2, 'Schacht n.i.O. voll'    
      ))))))) as IndiCom,
      
     If(tier1=1, Dual('läuft',1), Dual('läuft nicht',0)) as Lauf,
     
     [slaveref/refid],
      Pick(Match([slaveref/macid],'00A391','00A399'),'Brune4','Brune7') as Slave,
     %Key_data_CD43325B85172CA2
FROM [lib://C_Temp/MDE_Export.xml] (XmlSimple, table is [data/slavestate]);


tabMaschinenLaufzeiten:
LOAD *,
     If(Slave=Previous(Slave), Interval(Previous(DatumZeit)-DatumZeit)) as Dauer
Resident tabMaschinenLaufzeitenTemp
Order By Slave, %rowid desc;

DROP Table tabMaschinenLaufzeitenTemp;

hope this helps

Gruß

Marco

P.S.: Ich wusste gar nicht, dass WERMA auch etwas zur Datenerfassung des Ampelstatus im Angebot hat. Wieder etwas gelernt, danke.

[KarimBen]

Hello Taoufiq,
Hello Marco , 
many thanks for the support.
Both solutions help me a lot. 
Very good community, love it. 

@MarcoWedel  ja man kann Maschinen mit sensorik und slave von Werma nachrüsten, 
diese Slaves senden die erfassten Daten gesammelt an einen Master. 
Leider ist die Visualisierung der Software nicht gut genug weshalb wir nun mit qlik nachhelfen müssen. 
Falls Du Fragen zu dem Thema hast, gerne mit einer Nachricht melden. 

Best regards. 
Karim