1 Reply Latest reply: Oct 7, 2016 9:52 AM by Bill Markham RSS

    Merge or Concatenate Rows

    Joey Lutes

      I have built the following table:

       

      Queuing:

      LOAD

      TestId,

      Left(TestId, Len(Trim(TestId))-3) as TestIdKey,

      Value as ZeroMax

      Resident [datatable]

      Where AVal=1, BVal='House' ;

       

      concatenate

      LOAD

      TestId,

      Left(TestId, Len(Trim(TestId))-3) as TestIdKey,

      Value as FiveMin

      Resident [datatable]

      where AVal=2, BVal='Condo';

       

      It produces this:

       

      TestID           TestIDKey          ZeroMax             FiveMin

      123-aa                123                  12                      

      123-ab                123                                              7

      124-aa                124                   6

      124-ab                124                                              2

       

      I'm looking for the following:

      For any given TestIDKey,

      ZeroMax - FiveMin as Delta

       

      I've tried to create a table without TestID in it, only using TestIDKey, but it keeps the multiple rows no matter what I try.

      I'm trying to get it to do this, but it keeps showing 2 rows for each TestIDKey

       

      TestIDKey           ZeroMax               FiveMin          Delta

      123                         12                           7                5

      124                         6                             2                4

       

       

      Ultimately, I need that to link to TestID so that:

       

      TestID          TestIDKey           ZeroMax                  FiveMin          Delta

      123-aa              123                     12                                               5

      123-ab              123                                                    7                  5

      124-aa              124                      6                                               4

      124-ab              124                                                    2                 4


      Help?


      Thanks