Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a little problem in my loading script, here's the code that don't work:
Sales:
LOAD
[...fields, including the date DATAMOV...]
SQL SELECT *
FROM QLIK.SALES
ORDER BY DATEMOV ASC;
LET varMinDate = Num(Peek('DATEMOV', 0, 'Sales'));
LET varMaxDate = Num(Peek('DATEMOV', -1, 'Sales'));
LET varToday = num(today());
// Temporary calendar //
DateField:
Load
$(varMinDate) + RowNo() - 1 as Num,
date($(varMinDate) + RowNo() - 1) as TempDate
AutoGenerate
$(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,
Carlo A.
Hi Carlo,
You can load temp table containing only max date and min date
Temp:
Load
Max(Date) as Maxdate,
Min(Date) as MinDate
resident tempsales;
Let varMinDate=Fieldvalue(MaXDate,1);
Let varMinDate=Fieldvalue(MinDate,1);
Hope this will help
Vijay
Hi Carlo,
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:
tempSales:
LOAD ...
SQL SELECT *
FROM QLIK.SALES;
Sales:
NoConcatenate
LOAD *
Resident tempSales
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:
LET varMinDate = Num(Peek('DATEMOV', 0, 'Sales'));
LET varMaxDate = Num(Peek('DATEMOV', -1, 'Sales'));
trying to find some other function to reach the same result
Hi Carlo,
You can load temp table containing only max date and min date
Temp:
Load
Max(Date) as Maxdate,
Min(Date) as MinDate
resident tempsales;
Let varMinDate=Fieldvalue(MaXDate,1);
Let varMinDate=Fieldvalue(MinDate,1);
Hope this will help
Vijay
Hi Vijay, I tried to use your solution and it works! thank you so much