Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,,,
Hi bilal,
You can try to change the start of the script to:
Hope this helps.
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
Hi bilal,
You can try to change the start of the script to:
Hope this helps.
A nice way to implement the needfil,
First sort descending and then take the first entry through peek() function.
FactBudgetTemp:
LOAD Max("FactBudget[RequestKey]") As "FactBudget[RequestKey]"
FROM
(qvd);
let vMaxRequest = FieldValue("FactBudget[RequestKey]",1);
Drop Table FactBudgetTemp;
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
Hi PFB code
FactBudgetTemp:
LOAD Max("FactBudget[RequestKey]") as MaxRequest;
LOAD Max("FactBudget[RequestKey]") as MaxRequest
FROM
(
let vMaxRequest =
peek('MaxRequest', 1,'FactBudgetTemp');
Drop
Table FactBudgetTemp;