Forum Discussion
Solved
Calculate time difference to previous matching row using If statements
Hello. I am trying to calculate the time difference between rows according to below rules.
if(TrmID=Previous(TrmID)
and Date(floor(TrDt))=previous(Date(floor(TrDt)))
and OrdID=Previous(OrdID),
Interval(timestamp(TrDt)-timestamp(Previous(TrDt)),'mm:ss'),0) as [Cycle time]
I should get a new column, with the difference between TrDt on RowX and RowY, as long as TrmID, Date and OrdID is the same.
In below case thedifference is (presented in a new column):
Row1 is empty/0 since there is nothing to compare to.
Row2 00:00:25 (hh:mm:ss) between row1 and row2.
Row3 00:00:27 (hh:mm:ss) between row2 and row3.
and so on.
Table loaded.
My load is as below, sorting by TrmID, and secondly TrID (unique number).
SELECT
TrID,
TrDt,
convert(varchar,TrDt,112) as Date,
OrdID,
TrmID,
ProdID
FROM Tr (NOLOCK)
WHERE convert(varchar,TrDt,112)=20201126 AND
ORDER BY TrmID, TrID;
I have this code in Qlikview but dont understand how to apply it in Power BI/PowerQuery.
LOAD
TrID,
TrDt,
OrdID,
TrmID,
UsrID,
ProdID,
ProdNm,
Batch;
SQL SELECT * FROM "FT".dbo.Tr where TrTp=6 AND TrDt>=DateAdd(m, -2, getdate()) AND Tr.OrdTp = 4 AND Tr.SysDel = 0 order by TrmID, TrID;
Ord:
LOAD
OrdID,
OrdSt;
//*;
SQL SELECT * FROM "FT".dbo.Ord;
mapOrderStatus:
Mapping LOAD Distinct
OrdID,
OrdSt
Resident Ord;
FACT:
LOAD
RecNo() as %KeyRow,
TrID,
if(TrmID=Previous(TrmID) and Date(floor(TrDt))= previous(Date(floor(TrDt))) and OrdID=Previous(OrdID),Interval(timestamp(TrDt)-timestamp(Previous(TrDt)),'mm:ss'),0) as [Cycle time],
if(TrmID=Previous(TrmID) and Date(floor(TrDt))= previous(Date(floor(TrDt))) and OrdID=Previous(OrdID),1,0) as _cntCycleTime,
if(TrmID=Previous(TrmID) and Date(floor(TrDt))= previous(Date(floor(TrDt))) and OrdID<>Previous(OrdID),Interval(timestamp(TrDt)-timestamp(Previous(TrDt)),'mm:ss'),0) as [Switch time],
if(TrmID=Previous(TrmID) and Date(floor(TrDt))= previous(Date(floor(TrDt))) and OrdID<>Previous(OrdID),1,0) as _cntSwitchTime,
if(Date(floor(TrDt))<> previous(Date(floor(TrDt))),1,0) as _flgNewDate,
ApplyMap('mapOrderStatusName',ApplyMap('mapOrderStatus',OrdID,''),'<n/a>') as [Order status],
TrmID as [Terminal ID],
OrdID as [Order no],
if(OrdID<>Previous(OrdID),1,0) as _flgNewOrder,
UsrID as [User ID],
ProdID as Item,
ProdNm as [Item name],
Batch,
Timestamp(TrDt) as TimeStamp,
Date(floor(TrDt)) as %Date,
time(TrDt) as Time,
1 as _cntProduct
Resident Tr order by TrmID,TrID;
drop tables Tr, Ord;
Thanks for any advice!
let Origine = Excel.Workbook(File.Contents("C:\Users\37332115\OneDrive - TIM\MyD2020\BI\lexit data.xlsx"), null, true), Sheet2_Sheet = Origine{[Item="Sheet2",Kind="Sheet"]}[Data], calcCycle=(tab)=> let n=Table.RowCount(tab), cyb=List.Buffer(tab[Time]), cyTime=List.Accumulate({1..n-1}, {}, (s,c)=>s&{cyb{c-1}-cyb{c}})&{null} in cyTime, #"Intestazioni alzate di livello" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]), #"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"TrDt", type datetime}, {"TrmID", Int64.Type}, {"ProdID", Int64.Type}, {"OrdID", Int64.Type}}), #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(Table.TransformColumnTypes(#"Modificato tipo", {{"TrDt", type text}}, "it-IT"), "TrDt", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"TrDt.1", "TrDt.2"}), #"Modificato tipo1" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"TrDt.1", type date}, {"TrDt.2", type time}}), #"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo1",{{"TrDt.1", "Date"}, {"TrDt.2", "Time"}}), #"Raggruppate righe" = Table.Group(#"Rinominate colonne", {"Date", "TrmID", "OrdID"}, {{"CycleTime", each calcCycle(_)}}), #"Tabella CycleTime espansa" = Table.ExpandListColumn(#"Raggruppate righe", "CycleTime"), #"Modificato tipo2" = Table.TransformColumnTypes(#"Tabella CycleTime espansa",{{"CycleTime", type duration}}) in #"Modificato tipo2"just to make the code a little less (unnecessarily) redundant and perhaps more efficient, since the table to which you have to apply it is not small
16 Replies
Anonymous excel files with raw data and result table can be found on below link
let Origine = Excel.Workbook(File.Contents("C:\Users\xxx\lexit data.xlsx"), null, true), Sheet2_Sheet = Origine{[Item="Sheet2",Kind="Sheet"]}[Data], calcCycle=(tab)=> let n=Table.RowCount(tab), cols=Table.ColumnNames(tab), cyb=List.Buffer(tab[Time]), cyTime=List.Accumulate({1..n-1}, {}, (s,c)=>s&{cyb{c-1}-cyb{c}})&{null}, out=Table.FromColumns(Table.ToColumns(tab) & {cyTime}, cols & {"CycleTime"}) in out, #"Intestazioni alzate di livello" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]), #"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"TrDt", type datetime}, {"TrmID", Int64.Type}, {"ProdID", Int64.Type}, {"OrdID", Int64.Type}}), #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(Table.TransformColumnTypes(#"Modificato tipo", {{"TrDt", type text}}, "it-IT"), "TrDt", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"TrDt.1", "TrDt.2"}), #"Modificato tipo1" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"TrDt.1", type date}, {"TrDt.2", type time}}), #"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo1",{{"TrDt.1", "Date"}, {"TrDt.2", "Time"}}), #"Raggruppate righe" = Table.Group(#"Rinominate colonne", {"Date", "TrmID", "OrdID"}, {{"CycleTime", each calcCycle(_)[CycleTime]}}), #"Tabella CycleTime espansa" = Table.ExpandListColumn(#"Raggruppate righe", "CycleTime"), #"Modificato tipo2" = Table.TransformColumnTypes(#"Tabella CycleTime espansa",{{"CycleTime", type duration}}) in #"Modificato tipo2"
