Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem in order by while loading

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.

1 Solution

Accepted Solutions
vijay_iitkgp
Partner - Specialist
Partner - Specialist

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

View solution in original post

4 Replies
Not applicable
Author

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!

Not applicable
Author

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

vijay_iitkgp
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

Hi Vijay, I tried to use your solution and it works! thank you so much