Forum Discussion

vsinne's avatar
vsinne
Frequent Visitor
8 years ago
Solved

Merge rows contain different time values

Hello,

 

I have this database where all the agents are filling their time as and when they perform any task in the given ID #. This is resulting in duplication of ID# as shown below and their unique Time.

1) How can i sum up individual IDs time and merge the duplicate rows?

2) Their are other rows that has the same time but still showing duplicate IDs. How can i merge them as well without summing up the timings?

 

 

IDAgent NameTime in Seconds
85Ira7200
85Ira144000
85Ira14000
85Ira2400
85Ira40000
85Ira23000
85Ira14000
288Isha63
288Isha63
288Isha63
288Isha86400
288Isha86400
288Isha86400
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Or are you trying to get the following results vsinne?

     

     

    TableName = 

    SUMMARIZE (
        sourceTable,
        sourceTable[ID],
        sourceTable[Agent Name],
       "Total Time", SUMX(DISTINCT(sourceTable),sourceTable[Time in Seconds] )
    )

     

5 Replies

  • MarcoRotta's avatar
    MarcoRotta
    Resolver I
    8 years ago

    I'm assuming you want to sum the times and keep only a single record of the other fields, and you want to do that in your data model. If so, starting from the big combined table you already have, try to use the "Group by" option in the "Tranform" menu inside query editor. That would generate the output you require.

     

    Regards.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    From Modeling create a New Table as follow:

    TableName = 

    SUMMARIZE (
        sourceTable,
        sourceTable[ID],
        sourceTable[Agent Name],
       "Total Time", SUM(sourceTable[Time in Seconds])
    )

     

     

     

    Let me know if I didn't undertood your requirements correctly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Or are you trying to get the following results vsinne?

       

       

      TableName = 

    SUMMARIZE (
        sourceTable,
        sourceTable[ID],
        sourceTable[Agent Name],
       "Total Time", SUMX(DISTINCT(sourceTable),sourceTable[Time in Seconds] )
    )

       

      • vsinne's avatar
        vsinne
        Frequent Visitor
        8 years ago

        Yes, this is the result i am expecting to yield. how can i merge those rows that has just the duplicate values? thank you so much for your help.