topic variable in QlikView

variable

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

Hi,

Iam trying to execitive the below script ..

here iam trying to create a variable which should hold the max value for the field

callDate,

and in where condion for the field CallDate iam trying to pass that value of the variable .

but this is showing some error while executing .. can you please let me know where i am wrong

let vdate=max(CallDate);

Test :

LOAD

Department,

Revenue,

CallDate,

FROM

(qvd)

where CallDate=$(vdate);

thanks in advance

Re: variable

devarasu07 — Tue, 17 Oct 2017 09:50:19 GMT

Hi,

Try like this

let vdate=Date(max(CallDate));

Test :

LOAD

Department,

Revenue,

CallDate,

FROM (qvd)

where CallDate=date('$(vdate)');

Re: variable

Peter_Cammaert — Tue, 17 Oct 2017 09:52:55 GMT

In a load script, function Max() cannot be used outside a LOAD/SELECT statement. The way you apply this function, it will always return NULL. Assigning NULL to a variable will delete the variable (if it hasn't been created in the User Interface). So your WHERE claus will become something like:

WHERE CallDate = ;

which is incorrect syntax.

Good thing we can see the error message on your screen, without you showing us, ain't it?

Change the first statement into something like this. Note that CallDate must be present in an Internal Table before this will work.

MinMaxTable:

LOAD Max(CallDate) AS MaxCallDate

RESIDENT SourceTableWhereCallDateResides;

LET vdate = num(peek('MaxCallDate'));

DROP Table MinMaxTable;

Best,

Peter

[Edit] Improved formatting a bit to make the code examples stand out...

Re: variable

smilingjohn — Tue, 17 Oct 2017 09:55:48 GMT

it says script line error for

let vdate=Date(max(CallDate));

Re: variable

devarasu07 — Tue, 17 Oct 2017 10:03:27 GMT

Hi,

First you have to find the Max Date from your fact / source table. try like below

MaxDate: // it's used to get the max date from test.qvd file

Load Date(max(CallDate)) as MaxDate

FROM (qvd);

let vdate=Peek('MaxDate'); // fetch the value of a field from a previous record & let will store the value

drop table MaxDate;

Test :

LOAD

Department,

Revenue,

CallDate,

FROM (qvd)

where CallDate=date('$(vdate)');

Re: variable

smilingjohn — Tue, 17 Oct 2017 10:07:29 GMT

Got it Devarasu .

Thanks ...

Can you please look into my one of the post WHich is TOTAL...

will be really helpfulll

Re: variable

Tue, 17 Oct 2017 10:30:30 GMT

Hi,

Please find the attached QlikView document.

Your requirement will be resolved.

Thanks!