4 Replies Latest reply: Nov 6, 2017 2:47 AM by Luis Madriz

# want to use max value of a field by restricting in load editor ??

Hi,

There is a main table  FY18 and we have created a resident table tab1 to use GROUP BY function.

tab1:

CDG,

"Customer Group",

Text(if(Sum("Total Revenue")<0.25,'Tail Account (Rev< 1 Mn)',

if(Sum("Total Revenue")>=1.2,'Large Account (Rev> 5 Mn)',

If(Sum("Total Revenue")>=0.25 and sum("Total Revenue")<1.2,'Middle Account (Rev 1-5 Mn)')))) as account

Resident FY18

where Quarters=max(Quarters)

Group by CDG , Customer Group;

I want that grouping should perform over the data of maximum Quarter.

Therefore I use,

where Quarters=max(Quarters)

but this gives error.

Kindly help.

• ###### Re: want to use max value of a field by restricting in load editor ??

Hi D,

I would first get the max quarter first:

Temp:

Resident FY18;

Let vMaxQuarter = Peek('MaxQuarter');

Drop Table Temp;

Then, in your where clause you could use

where Quarters = \$(vMaxQuarter)

I hope this helps,

L

• ###### Re: want to use max value of a field by restricting in load editor ??

Thanks a lot.

But Quarter value is Q2.

Then how it would be able to calculate max of Q2 as it is a string . ??

• ###### Re: want to use max value of a field by restricting in load editor ??

Hi D,

When you create the quarter in FY18, do it as a Dual field and assign a number to it, for example:

If you have a DateField to use where quarters are calculated

Dual('Q'&Num(Ceil(Num(Month(DateField))/3)),QuarterStart(DateField)) as Quarters

or if you don't have that date but just the Q#, you could rewrite Quarters with something like this:

Dual(Quarters,Right(Quarters,1)) as Quarters

Ideally you should also have the Year with the quarter, but all depends what's available in your data

Max(Quarters) is going to bring a number so you need to redefine that number as a string, for example with 'Q'&Max(Quarters) so you can use it later

Cheers

L