How can I put a flag after the maximum number in this script?

[jblomqvist]

Hi all,

I have the following script:

LOAD

    *,

    If(CYMCounter >= 12, 1,0) as [12MonthFlag];

LOAD

*,

    AutoNumber(CustCode & '-' & Summary_Period, CustCode) as CYMCounter

It generates a table like below:

Colum 1 is CYMCounter

Column 2 is 12MonthFlag

How can I get it to place a "1" next to the maximum number in the CYMCounter after the number is gone beyond 12?

Any suggestions welcome!

[swuehl]

Add this to your script directly after your YourTable table load:

LEFT JOIN (YourTable)

      max(CYMCounter) as CYMCounter,

     1 as MaxCounterFlag

RESIDENT YourTable;

[jblomqvist]

Hi Swuhl,

Thank you for the reply. Is there a way to do this without a left join? I would like to do it in one table if possible

[swuehl]

There will be only one single resulting table after the join.

After rechecking your code, I think you need to group by CustCode when finding the max Counter:

LEFT JOIN (YourTable)

      CustCode,

      max(CYMCounter) as CYMCounter,

     1 as MaxCounterFlag

RESIDENT YourTable

GROUP BY CustCode;

If you don't like the join, you can use a resident load of your table sorted by CustCode and CYMCounter desc and then flag the first record for every change in CustCode.

RESULT:

LOAD *,

     if(peek(CustCode) <> CustCode, 1) as MaxCounterFlag

RESIDENT YourTable

ORDER BY CustCode, CYMCounter desc;

drop table YourTable;