topic Re: Variable not set in script in QlikView

Variable not set in script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I try to get a Load with a where condition like

LOAD
field1,
field2
Fractile(field3,0.95) as field3_95
Resident
LP_Transactions
where
field1 = $(LastRun)
group by field1,field2;

LastNum can be drawn earlier in the script from

LOAD Distinct
runNum
Resident
LP_Runs;

I try to get it into a variable like this:

Let LastRun = max(runNum);

But I never get "LastRun" filled.

In my dashboard I easily can get the correct number. into a text field.

Any suggestions?

tx Frank

Re: Variable not set in script

john_obrien — Tue, 13 Feb 2018 17:38:21 GMT

Try this for the "earlier in script" part:

tempMaxRun:

LOAD Distinct
Max(runNum) as MaxRunNum
Resident
LP_Runs;

I try to get it into a variable like this:

Let LastRun = Peek('MaxRunNum');

Drop Table tempMaxRun;

Re: Variable not set in script

Peter_Cammaert — Tue, 13 Feb 2018 17:48:32 GMT

The idea why your attempt fails and John's suggestion will work, is that in a load script, aggregation functions on fields can only be used inside a LOAD/SELECT statement. It's not that the aggregation functions won't work (they don't complain), it's that the fields themselves present a problem.

In the load script fields only exist within the context of a LOAD/SELECT statement. Even a Peek('FieldName') will actually peek in the column with that name in a specific table (or the last one you loaded), not in a field.

Re: Variable not set in script

shiveshsingh — Wed, 14 Feb 2018 06:27:26 GMT

Try this

T:LOAD Distinct
runNum
Resident
LP_Runs;

W: load max(runNUM) as MAX_NUM

resident T;

Let vMax = peek('MAX_NUM',0,'W');

Drop table W;

Re: Variable not set in script

Anonymous — Wed, 14 Feb 2018 08:22:51 GMT

Thanks for the background info.

I already tried peek() with no success. But now it works. Seemed to be a mistake in the notation....

Re: Variable not set in script

Anonymous — Wed, 14 Feb 2018 08:23:54 GMT

Thanks to all for your help. Everything ok now!