Unable to load data possibly due to varchar date field
I am facing an issue loading data on my app based on a cut-off day. I want to load data with date 4 days ago from today. The data comes from the data warehouse. The date field in the data warehouse is configured as varchar.
I tried using the following script with where clause to limit the data based on dates.
WHERE Date(Date#(std, 'YYYY-MM-DD'), 'DD/MM/YYYY') > $(vDate)
The variable is vDate = Date(Today()-4)
However, I get the following error Field 'std' not found
I'm unsure what could be wrong. I also realize that I might not be able to use a comparison operator like > because the original field in the table in the data warehouse isn't a date or a numeric value.
I have loaded the data based on another field and the 'std' field is available in the resulting table. However, when I try loading based on the 'std' field I keep getting the field not found error.
I would appreciate it if you could guide me on what could be going wrong. What can I do load the data based on the date field?
there are a couple of things you can do to pin down the problem:
so first of all, I assume you create the variable vDate before you are loading the data with the where statement.
if not place this before the data load statement.
the following thing you want to do is add a trace (print function) after the variable is created.
TRACE $(vDate);
after that try and only load the table with the where statement BUT comment out the where clause and add a first statement. then look if the field (std) is filled with data or not.
this will make sure that A data and field std are actually there and B it is in the right format.
one thing to keep in mind is that is you use this in a Select * FROM statement then native SQL is used instead of Qlik script.
I tried your suggestion and it help me narrow down a solution. Following your instructions I was able to load the data. I need to set up periodic reload task based on dates. I realized that the field type in the database table needs to be changed from text to date type in order to implement a date-based load.
