Load only one row for a group/set of data

[mansoorsheraz]

Hi,

I have the following data:

You can see abc.com and def.com have posted with two book numbers rest of the two domains have only one. I want the result set to be like this:

So that if there are duplicate book numbers, it should pick up the one with the latest one. I have tried to do this using concatenation but I dont think so that is the right approach as I may miss on something. Can someone let me know how to get the resultant table with the best way?

[BrunPierre]

As below

TempDataTable:
LOAD * Inline[
Domain,Date Reported Value,UOM,Quarters,Book Number,Value,Head
abc.com,2020-12-31,4,PKR, pk-2020,350000,Net Income
abc.com,2020-12-31,4,PKR, pk-2021,350000,Net Income
xyz.com,2020-12-31,4,PKR, pk-2020,225000,Net Income
def.com,2020-12-31,4,PKR, pk-2020,675000,Net Income
def.com,2020-12-31,4,PKR, pk-2021,675000,Net Income
qwe.com,2020-12-31,4,PKR, pk-2021,9800  ,Net Income

];

NoConcatenate
FinalDataTable:
LOAD Domain,
[Date Reported Value],
UOM,
Quarters,
Value,
Head,
LastValue([Book Number]) AS [Book Number]

Resident TempDataTable
Group by Domain,[Date Reported Value],UOM,Quarters,Value,Head;

DROP Table TempDataTable;

[RafaelBarrios]

Hi @BrunPierre 

Like the approach from @BrunPierre, it will depende on how you receive the data,

I would suggest the exact same script but with 

maxstring([Book Number]) as [Book Number]

Best,

[mansoorsheraz]

Thanks for your help, this approach will also have the double load, can't this be done in a single load?

[BrunPierre]

Try this front-end expression.

=FirstSortedValue(Value,-Aggr(Sum(Value),Domain),1)

[RafaelBarrios]

So, try preceding load

I stole the example from  @BrunPierre  that comes in handy for a quick test

FinalDataTable:
LOAD 
   Domain,
   [Date Reported Value],
   UOM,
   Quarters,
   Value,
   Head,
   LastValue([Book Number]) AS [Book Number],
  maxstring([Book Number]) AS [Book Number2]
Group by Domain,[Date Reported Value],UOM,Quarters,Value,Head;
LOAD * Inline [
   Domain,Date Reported Value,UOM,Quarters,Book Number,Value,Head
   abc.com,2020-12-31,4,PKR, pk-2020,350000,Net Income
   abc.com,2020-12-31,4,PKR, pk-2021,350000,Net Income
   xyz.com,2020-12-31,4,PKR, pk-2020,225000,Net Income
   def.com,2020-12-31,4,PKR, pk-2020,675000,Net Income
   def.com,2020-12-31,4,PKR, pk-2021,675000,Net Income
   qwe.com,2020-12-31,4,PKR, pk-2021,9800 ,Net Income];

Best,