Merge or Concatenate Rows

joey_lutes — Thu, 06 Oct 2016 19:51:44 GMT

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

Re: Merge or Concatenate Rows

Anonymous — Fri, 07 Oct 2016 13:52:30 GMT

Try joining instead of concatenating.

[Or maybe a mapping load and applymap]