Forum Discussion

Resolver I

5 years ago

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), ...

Need Help

Anonymous

5 years ago

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

lukasjar

Resolver I

5 years ago

Thank you but unfortunately I am not getting it to work with my sql database.

As I wrote, when I add more columns to the group by I just get null back from the calcCycle. Code below.

Cycle is null

let
    Source = Sql.Database("SSEHILSQL03\", "FT_Thule", [Query="SELECT  #(lf)    #(lf)TrID,#(lf)TrDt,#(lf)TrDt as DateTime,#(lf)TrmID,#(lf)OrdID,    #(lf)ProdID#(lf)FROM Tr (NOLOCK)#(lf)#(lf)WHERE convert(varchar,TrDt,112)=20201126 AND#(lf)(TrmID='2070517' OR TrmID='2070203')#(lf)AND TrTp=6 #(lf)AND Tr.OrdTp=4 #(lf)AND Tr.SysDel=0#(lf)ORDER BY TrmID, TrID;"]),
    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,
    #"Change type" = Table.TransformColumnTypes(#"Source",{
        {"TrID", Int64.Type},
        {"TrDt", type datetime},
        {"DateTime", type datetime},
        {"TrmID", Int64.Type},
        {"OrdID", Int64.Type}, 
        {"ProdID", Int64.Type}}),
    #"Split datetime" = Table.SplitColumn(Table.TransformColumnTypes(#"Change type", {{"TrDt", type text}}, "sv-SV"), "TrDt", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"TrDt.1", "TrDt.2"}),
    #"Change type of date and time" = Table.TransformColumnTypes(#"Split datetime",{{"TrDt.1", type date}, {"TrDt.2", type time}}),
    #"Rename date and time" = Table.RenameColumns(#"Change type of date and time",{{"TrDt.1", "Date"}, {"TrDt.2", "Time"}}),
    #"Group rows by calcCycle" = Table.Group(#"Rename date and time", {"Date", "DateTime", "TrID", "TrmID", "OrdID", "ProdID"}, {{"CycleTime", each calcCycle(_)}}),
    #"Expanded CycleTime" = Table.ExpandListColumn(#"Group rows by calcCycle", "CycleTime"),
    #"Tabella CycleTime espansa" = Table.ExpandListColumn(#"Expanded CycleTime", "CycleTime"),
    #"Modificato tipo2" = Table.TransformColumnTypes(#"Tabella CycleTime espansa",{{"CycleTime", type duration}})
in
    #"Modificato tipo2"

Do you have any clue why this happens?

Anonymous

Not applicable

5 years ago

I have several as to why this happens :-).

I can point you to something wrong, but without being able to test the code I cannot give you a complete fix.
I do not have much time ... so you should use goole (as I would)

La prima cosa che non va è l'istruzione duplicata ...

#"Expanded CycleTime" = Table.ExpandListColumn(#"Group rows by calcCycle", "CycleTime"),
#"Tabella CycleTime espansa" = Table.ExpandListColumn(#"Expanded CycleTime", "CycleTime"),

Controlla se la colonna DateTime esiste (forse la colonna si chiama Time)

#"Group rows by calcCycle" = Table.Group(#"Rename date and time", {"Date", "DateTime", "TrID", "TrmID", "OrdID", "ProdID"}, {{"CycleTime", each calcCycle(_)}}),

Controlla se la colonna DateTime esiste. Qua non si dovrebbe trovare nessuna colonna dateTime.

#"Change type" = Table.TransformColumnTypes(#"Source",{
{"TrID", Int64.Type},
{"TrDt", type datetime},
{"DateTime", type datetime},
{"TrmID", Int64.Type},
{"OrdID", Int64.Type},
{"ProdID", Int64.Type}}),

Per quanto riguarda l'aspetto dimensinale dovresti fornire in numero di righe e la dimensione tipica dei sottogruppi.

Quanti prodotti, quante transazioni per ogni prodotto e quante operazioni per ogni transazione, ecc

lukasjar
Resolver I
5 years ago
Hello.
Yes I have the datetime column, I have added it myself.

Can it be a issue that I try grouping by a unique number (TrID and DateTime is uniqe), causing the function not to work? I also removed the double grouping but it did no difference.
Anonymous
Not applicable
5 years ago
If you upload a complete excel I can try to rewrite/adapt the code.
If you provide also info about struct and dimensional aspect I can try to write a code to better fits your struct
lukasjar
Resolver I
5 years ago
I have uploaded them to dropbox, "lexit result 2" and "lexit data 2".

files for: lexit result 2, lexit data 2

Anonymous

Not applicable

5 years ago

let


    calcCycle=(tab)=>
    let
    n=Table.RowCount(tab),
    cols=Table.ColumnNames(tab),
    tabs=Table.Sort(tab,{"Time", Order.Descending}),
    cyb=List.Buffer(tabs[Time]), 
    cyTime=List.Accumulate({1..n-1}, {}, (s,c)=>s&{cyb{c-1}-cyb{c}})&{null},
    out=Table.FromColumns(Table.ToColumns(tabs)  & {cyTime}, cols & {"CycleTime"})
    in out,

    Origine = Excel.Workbook(File.Contents("C:\Users\37332115\OneDrive - TIM\MyD2020\BI\lexit data 2.xlsx"), null, true),
    Sheet2_Sheet = Origine{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Intestazioni alzate di livello" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"TrDt", type datetime}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "Date", each Date.From([TrDt] ), type date),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Aggiunta colonna personalizzata", "Time", each Time.From([TrDt]), type time),
    #"Raggruppate righe" = Table.Group(#"Aggiunta colonna personalizzata1", {"Date", "TrmID", "OrdID"}, {{"CycleT", each calcCycle(_)},{"Size", each Table.RowCount(_)}}),
    #"Tabella CycleT espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "CycleT", {"TrID", "ProdID", "ProdNm", "UsrID", "Batch", "Time", "CycleTime"}, {"TrID", "ProdID", "ProdNm", "UsrID", "Batch", "Time", "CycleTime"})
in
    #"Tabella CycleT espansa"

try this gradually :-).

First use 100k rows dataset then calc the time (the inrease should be linear) and multiply for yourdatasetSize/100k.