topic Use of functions in QlikView

Use of functions

AmCh — Sat, 16 Nov 2024 17:37:14 GMT

Hello all,
I have imported the following list 'Zeilen_PM'.
From this I wanted to determine the maximum value and then perform calculations within the FOR LOOP using the already calculated maximum value and generate a list.
Here is a simplified representation of a calculation:

DIRECTORY;
LOAD Zeilen_PM
FROM
[..]
(ooxml, embedded labels, table is Sheet1);

let vMAX=MAX(Zeilen_PM,1);

For Zahl=0 to 100
DATA:
LOAD
'$(Zahl)'*Zeilen_PM as WE
AutoGenerate 1;
next

But it comes to an error message 'Field vMax not found'.

Field not found error

Field 'Zeilen_PM' not found

DATA:
LOAD
'0'*Zeilen_PM as WE
AutoGenerate 1

What is the reason for this error?

Thanks in advance.
Best regards

Re: Use of functions

edwin — Tue, 12 Jan 2021 19:11:17 GMT

first, this will not work:
let vMAX=MAX(Zeilen_PM,1);

you cant use this operation on a variable, you need to determine the max of a field in a table and then let the variable have the value:

noconcatenate tmp: LOAD max(Zeilen_PM) as maxZeilen_PM FROM [..] (ooxml, embedded labels, table is Sheet1); let vMAX=peek('maxZeilen_PM',0, tmp);

Re: Use of functions

AmCh — Wed, 13 Jan 2021 12:10:16 GMT

thank you for your prompt answer.

Here the changed script:

noconcatenate tmp: LOAD max(Zeilen_PM) as maxZeilen_PM
FROM [O:\73_DataSource\201230_Zwischenschritte.xlsx] (ooxml, embedded labels, table is Sheet1);

let vMAX=peek('maxZeilen_PM',0, tmp);

For Zahl=0 to 100
DATA:
LOAD
'$(Zahl)'*vMAX as WE
AutoGenerate 1;
next

The same message still exists:

Field not found error

Field 'vMAX' not found

DATA:
LOAD
'0'*vMAX as WE
AutoGenerate 1

Is there anything to change within the loop?

Re: Use of functions

tresB — Wed, 13 Jan 2021 12:17:46 GMT

Try using dollar expansion for your variable like:

$(Zahl) * $(vMAX) as WE

Re: Use of functions

AmCh — Wed, 13 Jan 2021 12:44:00 GMT

It works perfectly!
Thank you.