Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Condition Where

Dear community

I dont know why I keep having the same problem when I do a condition on a number here is my script

aa.PNG

and here is the error

a.PNG

1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

Pleae check the value in vMaxDate is not empty by cheking the log file or putting $(vMaxDate) in a txtbox .

Try

Where NumDate>'$(vMaxDate)'

View solution in original post

11 Replies
Chanty4u
MVP
MVP

may be this

why # there?  what is the use?

WHERE [NumDate] >= max(NumDate)-29 ;

Anonymous
Not applicable
Author

I tried  this but also it didnt work

Chanty4u
MVP
MVP

tell me one thing

why # used?

and max(NumDate)   you placed in varibale?

Anonymous
Not applicable
Author

when I searched I find in a post that is recommended to use it #

jonathandienst
Partner - Champion III
Partner - Champion III

The error that you posted was by the expansion returning null. You cannot evaluate a Max() in this context.

Is not clear what you are trying to do - (1) you want a to use a field NumDate already loaded into QS, or (2) a field NumDate in the source database?

If (1), then you need to compute the Max value in a temp table, and Peek() the value into a variable.

         T_MaxDate:

         LOAD Max(NumDate) - 29 as MaxDate Resident Data;

         Let vMaxDate = Peek('MaxDate ');

         Drop Table T_MaxDate;


         SQL SELECT ....

         Where  NumBase > $(vMaxDate);


If (2), you will need to modify the SQL expression with a nested subquery or a join to compare NumDate and Max(NumDate)


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

>>when I searched I find in a post that is recommended to use it #


This is what that probably meant

Where  NumBase > $(#vMaxDate);


The # forces the vMaxDate dollar expansion to be numerical, which is not really necessary here.


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

What I actually want to do is to extract the data where the date > max(date)-29

so it didnt work when I did such a comparison and now I am stuck

Could you explain to me more what I should be doing in this case

^_^

sasiparupudi1
Master III
Master III

You are tying to use the alias in where condition

try using the actual field name

basically numDate will not be visible to the where condition. Try something like below


T1:

Load


*

Date#(deliverd#Date) as numDate

From Your Excel document;

Check if the numdate has the correct numeric date values

if it does

Max_Date:

LOAD Max(NumDate) as MaxDate

Resident T1;


Let vMaxDate = Peek('MaxDate ')-29;


Final:

Noconatenate Load


*


Where numDate>$(vMaxDate)

;

Drop Table T1;


Anonymous
Not applicable
Author

thank you for your help

now I start receiving this error

aaa.PNG