Create Deduped Table with counts and dimensions from original not deduped table

[Asuod_]

Hello, 

I am trying to created a deduped table with counts and measures from non deduped table.

I would like to accomplish the following:

• in the deduped table it always pulls the ID and all the fields with the max date and every time it refreshes it overrides the previous ID row
• add the min date as a column to the ID
• Count how many reports that ID has been on and add that count as a column

Original Table:

ID	Report Date	Amount	Category
1	9/14/2022	20	Clothing
1	9/13/2022	20	Clothing
1	9/12/2022	20	Clothing
1	9/11/2022	20	Clothing
2	9/10/2022	100	Furniture
2	9/9/2022	100	Furniture
2	9/8/2022	100	Furniture
2	9/7/2022	100	Furniture

what im looking to accomplish, Deduped Table:

ID	Report Date	Amount	Category	Min Report date	Report Count
1	9/14/2022	20	Clothing	9/11/2022	4
2	9/10/2022	100	Furniture	9/4/2022	6

Appreciate any help and guidance. 

[SerhanKaraer]

Hello,

You can achieve it by utilizing FirstSortedValue and Count. Beware of the values in sort weight for maximum report date records. They are all negative, meaning desc order.

Data:
LOAD ID,
FirstSortedValue("Report Date",-"Report Date") as "Report Date",
FirstSortedValue(Amount,-"Report Date") as Amount,
FirstSortedValue(Category,-"Report Date") as Category,
FirstSortedValue("Report Date","Report Date") as "Min Report Date",
Count(ID) as "Report Count"
GROUP BY ID;
LOAD * INLINE [
ID Report Date Amount Category
1 9/14/2022 20 Clothing
1 9/13/2022 20 Clothing
1 9/12/2022 20 Clothing
1 9/11/2022 20 Clothing
2 9/10/2022 100 Furniture
2 9/9/2022 100 Furniture
2 9/8/2022 100 Furniture
2 9/7/2022 100 Furniture
] (delimiter is '\t');