topic Get Maximum of integer field in a variable in QlikView

Get Maximum of integer field in a variable

Wed, 24 Sep 2014 11:34:08 GMT

Hi,

I'm trying to load data from a QVD with the maximum request number. my following script is failing:

FactBudgetTemp:
LOAD "FactBudget[RequestKey]"
FROM

(qvd);


let vMaxRequest = Max("FactBudget[RequestKey]");
Drop Table FactBudgetTemp;

'Budget Fact':
LOAD "FactBudget[DateKey]" as DateKey,
     "FactBudget[RequestKey]" as RequestKey,
     "FactBudget[AccountKey]" as AccountKey,
     "FactBudget[EtemadAmount]" as 'الاعتماد',
     "FactBudget[FromAmount]" as 'من',
     "FactBudget[ToAmount]" as 'إلى',
     "FactBudget[EtemadAfterAmount]" as 'الاعتماد بعد التعديل',
     "FactBudget[DirectConsumeAmount]" as 'المنصرف المباشر',
     "FactBudget[ErtebatAmount]" as 'الارتباط',
     "FactBudget[ConsumedErtebatAmount]" as 'المنصرف من الارتباط',
     "FactBudget[RemErtebatAmount]" as 'المتبقي من الارتباط',
     "FactBudget[TaklofaAmount]" as 'التكلفة',
     "FactBudget[ConsumedTaklofaAmount]" as 'المنصرف من التكلفة',
     "FactBudget[RemTaklofaAmount]" as 'المتبقي من التكلفة',
     "FactBudget[ConsumedAllClasses]" as 'المنصرف لكل الابواب'
  
FROM

(qvd)
WHERE "FactBudget[RequestKey]" = $(vMaxRequest);

What is the problem?

Thanks,,,

Re: Get Maximum of integer field in a variable

Anonymous — Wed, 24 Sep 2014 11:39:55 GMT

try:

sort your FactBudgetTemp Table by RequestKey

then do

Let vMaxRequest = peek('FactBudgetTemp',0,'FactBudget[RequestKey]')

where 0 is the first datarow and -ö1 the last

(depends on your sort order)

maybe you need to do the sort in an additional temporary table if sort cannot be enhanced

when you read the qvd

Re: Get Maximum of integer field in a variable

rubenmarin — Wed, 24 Sep 2014 11:41:32 GMT

Hi bilal,

You can try to change the start of the script to:

FactBudgetTemp:
LOAD Max("FactBudget[RequestKey]") as MaxRequest;
LOAD "FactBudget[RequestKey]"
FROM
(qvd);
let vMaxRequest = FieldValue('MaxRequest', 1);
Drop Table FactBudgetTemp;

Hope this helps.

Re: Get Maximum of integer field in a variable

sujeetsingh — Wed, 24 Sep 2014 11:41:50 GMT

A nice way to implement the needfil,

First sort descending and then take the first entry through peek() function.

Re: Get Maximum of integer field in a variable

anbu1984 — Wed, 24 Sep 2014 11:44:46 GMT

FactBudgetTemp:

LOAD Max("FactBudget[RequestKey]") As "FactBudget[RequestKey]"

FROM

(qvd);

let vMaxRequest = FieldValue("FactBudget[RequestKey]",1);

Drop Table FactBudgetTemp;

Re: Get Maximum of integer field in a variable

marcus_sommer — Wed, 24 Sep 2014 11:47:27 GMT

You couldn't read the max-value from a table in a variable unless you load these table per resident and sorting to your field and could then per peek() read the first/last row from these table.

Alternatively is to make an aggregation-load, like:

load max(distinct date) as maxDate From xyz;

But have a look here: “Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook

- Marcus

Re: Get Maximum of integer field in a variable

perumal_41 — Wed, 24 Sep 2014 11:47:45 GMT

Hi PFB code

FactBudgetTemp:

LOAD Max("FactBudget[RequestKey]") as MaxRequest;
LOAD Max("FactBudget[RequestKey]") as MaxRequest
FROM

(qvd);
let vMaxRequest =

peek('MaxRequest', 1,'FactBudgetTemp');

Drop

Table FactBudgetTemp;