topic Re: Sum of Productive Runtime of Maschines in App Development

Sum of Productive Runtime of Maschines

KarimBen — Mon, 10 Jan 2022 15:17:57 GMT

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

Re: Sum of Productive Runtime of Maschines

Taoufiq_Zarra — Tue, 11 Jan 2022 12:51:26 GMT

@KarimBen can you elaborate ?

from your sample :

can you share the expected output ?

Re: Sum of Productive Runtime of Maschines

KarimBen — Tue, 11 Jan 2022 16:03:28 GMT

Hello Taoufiq ,

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

Thats it. 🙂

Re: Sum of Productive Runtime of Maschines

KarimBen — Tue, 11 Jan 2022 17:03:40 GMT

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".

Re: Sum of Productive Runtime of Maschines

Taoufiq_Zarra — Tue, 11 Jan 2022 19:59:22 GMT

@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 :

Re: Sum of Productive Runtime of Maschines

MarcoWedel — Tue, 11 Jan 2022 21:49:51 GMT

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.

Re: Sum of Productive Runtime of Maschines

KarimBen — Tue, 11 Jan 2022 23:22:01 GMT

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