Discussion Board for collaboration on QlikView Scripting.
I have a little problem in my loading script, here's the code that don't work:
[...fields, including the date DATAMOV...]
SQL SELECT *
ORDER BY DATEMOV ASC;
LET varMinDate = Num(Peek('DATEMOV', 0, 'Sales'));
LET varMaxDate = Num(Peek('DATEMOV', -1, 'Sales'));
LET varToday = num(today());
// Temporary calendar //
$(varMinDate) + RowNo() - 1 as Num,
date($(varMinDate) + RowNo() - 1) as TempDate
$(varMaxDate) - $(varMinDate) + 1;
This code will create the temporary table DateField that I use to build a calendar.
The problem is that varMinDate and varMaxDate are both <NULL> and the execution throws an exception, but if I remove the line "ORDER BY DATEMOV ASC", it will work fine.
Unfortunately I cannot assume that the rows in the table "Sales" are ordered per DATEMOV in the source database.
The table Sales could have up to 3.000.000 rows, but I think this is not the problem.
So anyone knows a method to correctly initialize varMinDate and varMaxDate, or a possible explanation of why I get this error?
Thank you in advance,
Go to Solution.
You can load temp table containing only max date and min date
Max(Date) as Maxdate,
Min(Date) as MinDate
Hope this will help
What you might face is that the "order by" clause doesn't work when loading data from a qvd file.
But here your data is loaded directly from database and I'm not sure if it's the same case (will have to try it tomorrow at work).
As a solution, you can load your data first into a temp table without the "order by" clause, then do a resident load where you can order. It would go like this:
Order By DATEMOV;
DROP Table tempSales;
LET varMinDate = ... //continue with your actual script
Hope this helps!
Thank you, this could be a solution, but it will result in a slower loading, so I am still trying to solve this working on the initialization:
trying to find some other function to reach the same result
Hi Vijay, I tried to use your solution and it works! thank you so much