Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
ashmitp869
Creator II
Creator II

variable in the where clause not working in the qlikview script.

Hi there,

Please help me out with the below script -

 

let vDayPrev = 31;

TMP:
LOAD
MAX([Date]) as MaxDate
FROM [$(vL.QVDPath)\QVD \File.qvd]
(qvd);
let vMD_ToC = Num(Peek('MaxDate', 0, 'TMP'));

[ToC]:
LOAD
MakeDate(Year([ Date]),Month([Date]),Day([ Date])) as [ReportingDate],
[Identifier],
count(1) as count_toc
from
[$(vL.QVDPath)\QVD \File.qvd]
(qvd)
WHERE
[Date] >= '$(vMD_ToC)'-$(vDayPrev) AND [ Date] <= '$(vMD_ToC)'
group by [ Identifier],[Date];

 

What is wrong with the script . Please suggest .

My req , I want Maxdate - 31 days in the where condition i.e 

WHERE
[Date] >= '$(vMD_ToC)'-$(vDayPrev) AND [ Date] <= '$(vMD_ToC)'

 

Thanks

Labels (1)
1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

Since I cannot see your data, I cannot tell for sure... but I would try a Where-clause like this instead:

WHERE
[Date] >= $(#vMD_ToC)-$(#vDayPrev) AND [Date] <= $(#vMD_ToC)

Also, you have an error in several places in the script: [Date] is not the same as [ Date]. You cannot have an extra space inside the quotes. What's inside the quotes must be exactly the same as the field name.

View solution in original post

1 Reply
hic
Former Employee
Former Employee

Since I cannot see your data, I cannot tell for sure... but I would try a Where-clause like this instead:

WHERE
[Date] >= $(#vMD_ToC)-$(#vDayPrev) AND [Date] <= $(#vMD_ToC)

Also, you have an error in several places in the script: [Date] is not the same as [ Date]. You cannot have an extra space inside the quotes. What's inside the quotes must be exactly the same as the field name.