topic Re: Max - One field or another in QlikView

Max - One field or another

Anonymous — Wed, 04 Mar 2015 15:51:23 GMT

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.

Re: Max - One field or another

sunny_talwar — Wed, 04 Mar 2015 16:07:55 GMT

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,

Re: Max - One field or another

rwunderlich — Wed, 04 Mar 2015 16:44:49 GMT

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

Re: Max - One field or another

sunny_talwar — Wed, 04 Mar 2015 17:29:30 GMT

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

Thanks for sharing this method.

Best,

Re: Max - One field or another

Anonymous — Wed, 04 Mar 2015 18:01:48 GMT

Thank you both.

Re: Max - One field or another

rwunderlich — Wed, 04 Mar 2015 19:33:17 GMT

Please mark something as correct to close the thread.

-Rob

Re: Max - One field or another

sunny_talwar — Wed, 04 Mar 2015 19:43:30 GMT

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,

Re: Max - One field or another

Anonymous — Wed, 04 Mar 2015 20:04:30 GMT

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