Max function partition by a dimension value

[Abhi_WP]

PayNo DayDt	Row No	Max(Row No)
111824|20082020	1	4
111824|20082020	2	4
111824|20082020	3	4
111824|20082020	4	4
2587|17052020	1	2
2587|17052021	2	2

I have first two columns in my script and I want to populate  max of row no (3rd column) for each unique combination of PayNo DayDt. Any help will be apprecaited.

[lironbaram]

hi 

you can use 

max(total <PayNo DayDt>Row No)

[Abhi_WP]

Hi, 

Is it missing one bracket ?

I am getting below error

Error in expression:

')' expected

[tresesco]

It is not missing ')', however, it has other issues. If your field name contains spaces it has to be surrounded by double quotes or square brackets or any valid other quotes. Although, I guess resolving this alone would not give you the right result. You need to create a new field in the script, like:

Load Subfield([PayNo DayDt], 1) as PayNo

And then use this PayNo in the expression/dimension for desired result.

[Abhi_WP]

I have square brackets around the field. This is my current expression

[Temp2 Raw Timesheet Dim]:
Load *,
Max(TOTAL <[PayNo DayDt]> DayRunningCount) as LastRowValue,
Resident [Temp1 Raw Timesheet Dim];

As suggested by you, I will create this new field and will see the result.

Thanks

[tresesco · Accepted solution]

You are using this expression in script? It was not meant for that. In script you can't use TOTAL; if you use aggregation function like max(), you have to use it in combination with Group By clause. 

And that's why it is very important to explain your requirement more in details; giving business context...and that could help you getting faster solution; minimize helping people here having to come back for unnecessary follow up queries for clarifications.  

[Abhi_WP]

Thanks for your suggestion. 

I created a temp table and used group by function to take the max of it. And then, joined it back to the main table.