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
