Max - One field or another

[Anonymous]

Have a table with fields:

EmployeeName     Product     Sales.CurrentYr     Sales.PrevYr

Need to find Max of Sales by EmployeeName and Product if they exist under Sales.CurrentYr column otherwise find Max from Sales.PrevYr filed.

Need to do in load script.

Thank you for any help.

[sunny_talwar]

You can use Group By statement to find the max for individual columns (Sales.CurrentYr and Sales.PrevYr) may be like this

LOAD EmployeeName

          Product

          Max(Sales.CurrentYr) as MaxSales.CurrentYr

          Max(Sales.PrevYr) as MaxSales.PrevYr

Resident yourTable

Group By EmployeeName, Product;

and then use a if Statement like this

If(not IsNull(MaxSales.CurrentYr), MaxSales.CurrentYr, MaxSales.PrevYr) as MaxSales

HTH

Best,

S

[rwunderlich · Accepted solution]

I think it may be simpler using alt()

LOAD EmployeeName

          Product

          Max(alt(Sales.CurrentYr, Sales.PrevYr)) as MaxSales

Resident yourTable

Group By EmployeeName, Product;

-Rob

[sunny_talwar]

I think you are right. This definitely is a simpler method of doing this.

Thanks for sharing this method.

Best,

S

[Anonymous]

Thank you both.

[rwunderlich]

Please mark something as correct to close the thread.

-Rob

[sunny_talwar]

Mark my answer as correct, Rob already have lot of points .

Just kidding. I agree with Rob. Mark one of the answers as correct answer so that other can benefit from this thread in the future.

Best,

S

[Anonymous]

I wish I could mark both them as correct but again S and Rob much appreciate it.