Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rochdkhouloud
New Contributor

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
Honored Contributor III

Re: Condition Where

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

Try

Where NumDate>'$(vMaxDate)'

11 Replies
sureshqv
Esteemed Contributor III

Re: Condition Where

may be this

why # there?  what is the use?

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

rochdkhouloud
New Contributor

Re: Condition Where

I tried  this but also it didnt work

sureshqv
Esteemed Contributor III

Re: Condition Where

tell me one thing

why # used?

and max(NumDate)   you placed in varibale?

rochdkhouloud
New Contributor

Re: Condition Where

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

MVP
MVP

Re: Condition Where

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)


MVP
MVP

Re: Condition Where

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


rochdkhouloud
New Contributor

Re: Condition Where

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
Honored Contributor III

Re: Condition Where

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;


rochdkhouloud
New Contributor

Re: Condition Where

thank you for your help

now I start receiving this error

aaa.PNG

Community Browser