Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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 :
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 can you elaborate ?
from your sample :
can you share the expected output ?
Hello Taoufiq ,
thanks for replying.
I want the sum of the individual status changes.
Thats it. 🙂
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".
@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 :
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.
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