topic Re: How can I put a flag after the maximum number in this script? in QlikView

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

jblomqvist — Mon, 22 Jun 2015 20:19:55 GMT

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!

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

swuehl — Mon, 22 Jun 2015 20:23:51 GMT

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

LEFT JOIN (YourTable)

max(CYMCounter) as CYMCounter,

1 as MaxCounterFlag

RESIDENT YourTable;

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

jblomqvist — Mon, 22 Jun 2015 20:27:34 GMT

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

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

swuehl — Mon, 22 Jun 2015 20:49:59 GMT

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;