Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
This is the thing:
Imaging that I have this fields:
Group | Category | Date | Amount |
Now I want to calculate the max(Amount) and get the date of that maximum grouping by Group and Category.
And this is the problem:
If I do the group by with Group and Category, I don´t know in which date I had the maximum, the solution I thought is doing a link with the amount or with a key composed by Group&Category&Amount but I don´t think it would be the best solution, overall if there are repetead maximums.
And I can´t group by Group, Category and Date, because I want the maximum amount of those Dates and not the maximum per day.
The date must be only an added information field.
If is not clear enough, ask me please.
I´m doing all in the load script, FYI.
Any ideas??
Thank´s
You have only one Amount record per Date, Category and Group or do you need to sum amounts prior getting the max?
I think you could try using max(Amount) and FirstSortedValue:
LOAD
Group,
Category,
max(Amount) as MaxAmount,
FirstSortedValue(Distinct Date, -Amount) as MaxAmountDate
resident YourTable group by Group, Category;
You have only one Amount record per Date, Category and Group or do you need to sum amounts prior getting the max?
I think you could try using max(Amount) and FirstSortedValue:
LOAD
Group,
Category,
max(Amount) as MaxAmount,
FirstSortedValue(Distinct Date, -Amount) as MaxAmountDate
resident YourTable group by Group, Category;
As successful as ever!
Thank you, it´s a great function this FirstSortedValue.
Regards,
Chema