topic Re: Max Function not working in script in QlikView

Max Function not working in script

Sat, 25 Oct 2014 14:53:47 GMT

I'm working on an incremental load script and need to obtain the last Primary Key loaded (originates from an identify column in SQL).

In the below script I can not get Max(PriKeyItem) to return any results into the vMaxKey variable. If I change Max to Count results are obtained.

Item:

LOAD *

FROM (qvd);

MaxPriKey:

LOAD Max('PriKeyItem') AS "maxPK" Resident Item;

LET vMaxKey = FieldValue('maxPK',1);

//LET vMaxKey=peek('maxPK',-1,'MaxPriKey');

Thanks in advance for any suggestions -

Brett

Re: Max Function not working in script

MK_QSL — Sat, 25 Oct 2014 15:33:48 GMT

TableName:

LOAD *

FROM (qvd);

MaxPriKey:

LOAD Max(PriKeyItem) AS maxPK Resident TableName;

LET vMaxKey = Peek('maxPK',0,'MaxPriKey');

Re: Max Function not working in script

SunilChauhan — Sat, 25 Oct 2014 15:52:18 GMT

dont use single quotes in max function otherwise it will take it as text and max will not work use below

Item:

LOAD *

FROM (qvd);

MaxPriKey:

LOAD Max(PriKeyItem) AS "maxPK" Resident Item;

LET vMaxKey = FieldValue('maxPK',1);

//LET vMaxKey=peek('maxPK',-1,'MaxPriKey');

hope this helps

Re: Max Function not working in script

Sat, 25 Oct 2014 15:58:46 GMT

Thanks guys. I could have sworn the count function worked with the single quotes (and returned a value > 1) but maybe not.

Re: Max Function not working in script

preminqlik — Sat, 25 Oct 2014 15:59:50 GMT

if your primary key is in string format then use the following

LOAD Maxstring(PriKeyItem) AS "maxPK" Resident Item;

Re: Max Function not working in script

chematos — Mon, 27 Oct 2014 18:32:09 GMT

Use distinct for the second table, you could be getting more than one value for Max(PriKeyItem)

TableName:

LOAD *

FROM (qvd);

MaxPriKey:

NoConcatenate

LOAD Distinct Max(PriKeyItem) AS maxPK Resident TableName;

LET vMaxKey = Peek('maxPK');

hope this helps.