variable

[smilingjohn]

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,

ID

FROM

(qvd)

where CallDate=$(vdate);

thanks in advance

[devarasu07]

Hi,

Try like this

let vdate=Date(max(CallDate));

Test :

LOAD

Department,

Revenue,

CallDate,

ID

FROM (qvd)

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

[Peter_Cammaert]

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...

[smilingjohn]

it says script line error  for

let vdate=Date(max(CallDate));

[devarasu07 · Accepted solution]

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,

ID

FROM (qvd)

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

[smilingjohn]

Got it Devarasu .

Thanks ...

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

will be really helpfulll

[#7]

Hi,

Please find the attached QlikView document.

Your requirement will be resolved.

Thanks!