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

[Anonymous]

Try joining instead of concatenating.

[Or maybe a mapping load and applymap]