Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help to get next timestamp from first timestamp

Hi guys,

 

I am trying to get the pairs of "00 Initialization: Assigning all transaction item variables" and "Transaction number X was successfully processed.", and their time stamps. There might be a record of "00 Initialization: Assigning all transaction item variables" in between, that should be ignored. In the same day and for the same JobKey, I need to find the timestamp from the first instance of A to the first instance of B. In a day, there might be more than 1 pair, as indicated by the highlighted-blue (I need those time stamp too).

 

A: 00 Initialization: Assigning all transaction item variables

B: Transaction number X was successfully processed.

link to file: https://drive.google.com/file/d/1guGmTxp8Z2lhxMWTrxmf7pnhrRDQxTyR/view?usp=sharing

 

Table 1

00 Initialization: Assigning all transaction item variables2/26/21 2:55 AM 
00 Initialization: Transaction type: Permits. Performing Local Council Permit Renewals2/26/21 2:55 AM 
10 Initialize Matrix Tables: Initializing config variables to be used for processing2/26/21 2:55 AM 
20 Split Submissions Entry: Navigating to Invoices to Split2/26/21 2:55 AM 
20 Split Submissions Entry: Finding submission entry to split2/26/21 2:55 AM 
20 Split Submissions Entry: Submission to split found2/26/21 2:55 AM 
........  
00 Initialization: Transaction type: Permits. Performing Local Council Permit Renewals2/26/21 2:58 AM 
.......  
Transaction number 2 was successfully processed.2/26/21 3:03 AM 
.......  
00 Initialization: Assigning all transaction item variables2/26/21 3:03 AM 
...........  
Transaction number 3 was successfully processed.2/26/21 3:06 AM 

 

I have tried with the following DAX, but NP AHT GetFirstAssignAllTransactionItemVariables only returns the first timestamp of A (belonging to the red pair). I have inserted the link to the excel with all the column headers. Assistance is much appreciated.

 

NP AHT GetFirstAssignAllTransactionItemVariables =
MINX(
FILTER(
Logs,
Logs[ProcessName] = "RPANET009.Esker.Payments.Management.Performer_RPANET009 Application, Payments Management of Local Council Permits"
&& Logs[JobKey] = EARLIER(Logs[JobKey])
&& Logs[TimeStamp] < EARLIER(Logs[TimeStamp])
&& SEARCH("00 Initialization: Assigning all transaction item variables", [Message],,BLANK())>0
),
Logs[TimeStamp]
)
 
NP AHT GetNextSuccessfulProcessed =
MINX(
FILTER(
Logs,
Logs[ProcessName] = "RPANET009.Esker.Payments.Management.Performer_RPANET009 Application, Payments Management of Local Council Permits"
&& Logs[JobKey] = EARLIER(Logs[JobKey])
&& Logs[TimeStamp] >= EARLIER(Logs[TimeStamp])
&& SEARCH("was successfully processed.", [Message],,BLANK())>0
),
Logs[TimeStamp]
)
  • AlB's avatar
    AlB
    5 years ago

    Anonymous 

    It is, but I'd certainly recommend to do it in PQ. Way simpler. Create a new calculated table. See attached file

    NewTable = 
VAR baseT_ =
    FILTER (
        SUMMARIZE (
            Table1,
            Table1[JobKey],
            Table1[DayStamp],
            Table1[Message],
            Table1[TimeStamp]
        ),
        Table1[Message] = "00 Initialization: Assigning all transaction item variables"
            || (
                FIND ( "Transaction number", [Message], 1, 0 ) > 0
                    && FIND ( "was successfully processed", [Message], 1, 0 )
            )
    )
RETURN
    FILTER (
        baseT_,
        VAR previousTrans_ =
            MAXX (
                FILTER (
                    baseT_,
                    [TimeStamp] < EARLIER ( [TimeStamp] )
                        && FIND ( "Transaction number", [Message], 1, 0 ) > 0
                        && FIND ( "was successfully processed", [Message], 1, 0 ) > 0
                        && [DayStamp] = EARLIER ( [DayStamp] )
                ),
                [TimeStamp]
            )
        VAR previousIni_ =
            MAXX (
                FILTER (
                    baseT_,
                    [TimeStamp] < EARLIER ( [TimeStamp] )
                        && [Message] = "00 Initialization: Assigning all transaction item variables"
                        && [DayStamp] = EARLIER ( [DayStamp] )
                ),
                [TimeStamp]
            )
        RETURN
            IF (
                [Message] = "00 Initialization: Assigning all transaction item variables",
                previousIni_ <= previousTrans_,
                TRUE ()
            )
    )

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous 

    It is, but I'd certainly recommend to do it in PQ. Way simpler. Create a new calculated table. See attached file

    NewTable = 
VAR baseT_ =
    FILTER (
        SUMMARIZE (
            Table1,
            Table1[JobKey],
            Table1[DayStamp],
            Table1[Message],
            Table1[TimeStamp]
        ),
        Table1[Message] = "00 Initialization: Assigning all transaction item variables"
            || (
                FIND ( "Transaction number", [Message], 1, 0 ) > 0
                    && FIND ( "was successfully processed", [Message], 1, 0 )
            )
    )
RETURN
    FILTER (
        baseT_,
        VAR previousTrans_ =
            MAXX (
                FILTER (
                    baseT_,
                    [TimeStamp] < EARLIER ( [TimeStamp] )
                        && FIND ( "Transaction number", [Message], 1, 0 ) > 0
                        && FIND ( "was successfully processed", [Message], 1, 0 ) > 0
                        && [DayStamp] = EARLIER ( [DayStamp] )
                ),
                [TimeStamp]
            )
        VAR previousIni_ =
            MAXX (
                FILTER (
                    baseT_,
                    [TimeStamp] < EARLIER ( [TimeStamp] )
                        && [Message] = "00 Initialization: Assigning all transaction item variables"
                        && [DayStamp] = EARLIER ( [DayStamp] )
                ),
                [TimeStamp]
            )
        RETURN
            IF (
                [Message] = "00 Initialization: Assigning all transaction item variables",
                previousIni_ <= previousTrans_,
                TRUE ()
            )
    )

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks AlB! Just applied the solution to my company's real-data and it worked like a charm! 5 stars to you my friend!

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    You can do this in the query editor. This would be the M code that reads the data from your Excel file and extracts the time stamps you need. See it all at work in the attached file.

     

    let
    Source = Excel.Workbook(File.Contents("d:\_system\Desktop\Forum\dashboard_network_permits_getAHT2.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"ID", Int64.Type}, {"SD", type datetime}, {"ED", type datetime}, {"JobKey", type text}, {"Source", Int64.Type}, {"Message", type text}, {"TimeStamp", type datetime}, {"ProcessName", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Message] = "00 Initialization: Assigning all transaction item variables" or (Text.Contains([Message], "Transaction number") and Text.Contains([Message], "was successfully processed")))),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "DayStamp", each Date.From([TimeStamp]), type date),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"TimeStamp", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"JobKey", "DayStamp", "Message"}, {{"TimeStamp", each List.Min([TimeStamp]), type datetime }}, GroupKind.Local)
in
    #"Grouped Rows"

     

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      hi AlB ,

       

      Tx. for your quick response! Is is possible to get the solution in DAX?

       

      figure 1 - correct pairing of A and B using DAX NP AHT GetFirstAssignAllTransactionItemVariables and NP AHT GetNextSuccessfulProcessed, from 2/26/2021 2:55 AM to 2/26/2021 3:03 AM.

       

      figure 2 - Incorrect pairing of A and B using DAX NP AHT GetFirstAssignAllTransactionItemVariables and NP AHT GetNextSuccessfulProcessed, from 2/26/2021 2:55 AM (supposed to be 2/26/21 3:03 AM ) to 2/26/2021 3:06 AM.