Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Friends
I have a situation for which the code is below:
TempTableA:
Load max([Temp ID]) AS [Temp ID],[Orig Temp ID] from TableA.qvd(qvd) group by [Orig Temp ID];
TableA:
Load * from TableA.qvd(qvd) where exists ([Temp ID]) and exists ([Orig Temp ID]);
Drop Table TempTableA:
So basically, what I am trying to do with this piece of code is that for each [Orig Temp ID] I want the corresponding max([Temp ID]) only.
SalesPerson Orig Temp ID Temp ID
John Doe 1 111
John Doe 1 222
John Doe-New 1 333
So therefore I want only the 3rd row to showup only in my output
So basically, what I am trying to do with this piece of code is that for each [Orig Temp ID] I want the corresponding max([Temp ID]) only.
Is using the wherexists a good option or is there an alternate best way to do?
Note: Temp ID is my primary Key
Thanks
Hi Rizwan,
Try this,
Data:
LOAD SalesPerson,
[Orig Temp ID],
[Temp ID]
FROM
TableA.qvd
(qvd);
Inner Join
Load [Orig Temp ID],
Max( [Temp ID]) as [Temp ID]
Resident Data Group by [Orig Temp ID];
Another option:
TableA:
LOAD FirstSortedValue(SalesPerson, -[Temp ID]) as SalesPerson
[Orig Temp ID],
Max([Temp ID]) as [Temp ID]
FROM TableA.qvd (qvd)
Group By [Orig Temp ID];