Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community
I dont know why I keep having the same problem when I do a condition on a number here is my script
and here is the error
Pleae check the value in vMaxDate is not empty by cheking the log file or putting $(vMaxDate) in a txtbox .
Try
Where NumDate>'$(vMaxDate)'
may be this
why # there? what is the use?
WHERE [NumDate] >= max(NumDate)-29 ;
I tried this but also it didnt work
tell me one thing
why # used?
and max(NumDate) you placed in varibale?
when I searched I find in a post that is recommended to use it #
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)
>>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.
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
^_^
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;
thank you for your help
now I start receiving this error