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 with resident sorting

I've been trying to load and sort a tmp table and create variable names out of dates. It seems to mostly work, however it is not properly sorting the dates in descending order. Below is my load script and the resulting table. Thanks for any help.

Directory;

Traffic:

LOAD ORIG_CUSTOMER,

     ORIG_CUSTOMER_ACCOUNT_MANAGER,

     ORIG_CUSTOMER_TYPE,

     TERM_CUSTOMER,

     TERM_CUSTOMER_ACCOUNT_MANAGER,

     TERM_CUSTOMER_TYPE,

     PRODUCT,

     WEEK_START,

     WEEK_END,

     Month(WEEK_START) as Start_Month,

     Week(WEEK_START) as Start_Week,

     Day(WEEK_START) as Start_Day,

     Month(WEEK_END) as End_Month,

     Week(WEEK_END) as End_Week,

     Day(WEEK_END) as End_Day,

     Year(WEEK_END) as End_Year,

     MINUTES as Inbound,

     MINUTES as Outbound,

     (MINUTES *2) as [Total Minutes]

FROM

[SOURCE.xlsx]

(ooxml, embedded labels, table is Sheet1);

TMP:

NoConcatenate load DISTINCT WEEK_START AS StartDT, WEEK_END as EndDT

resident Traffic

Order BY WEEK_START desc;

vLatestWkStart = DATE(PEEK('StartDT',0,'TMP'));

vLatestWkEnd = DATE(PEEK('EndDT',0,'TMP'));

vPrevWkStart = DATE(PEEK('StartDT',1,'TMP'));

vPrevWkEnd = DATE(PEEK('EndDT',1,'TMP'));

v52WkAgoStart =  DATE(PEEK('StartDT',51,'TMP'));

v52WkAgoEnd =  DATE(PEEK('EndDT',51,'TMP'));

Qlikview.png

1 Solution

Accepted Solutions
Not applicable
Author

So it seems I was able to fix this problem. I had to do this by first grouping together the field and then sort. Thanks for all your help though

TMP:

NoConcatenate load DISTINCT WEEK_START AS StartDT, WEEK_END as EndDT, count(1)

Resident IQNT_TRAFFIC

Group by WEEK_START, WEEK_END

Order BY WEEK_START desc;

View solution in original post

3 Replies
Sokkorn
Master
Master

Hi,

Let try in first load statement

.....

FROM

[SOURCE.xlsx]

(ooxml, embedded labels, table is Sheet1) ORDER BY WEEK_START DESC;

Regards,

Sokkorn

maksim_senin
Partner - Creator III
Partner - Creator III

Also you may try playing with data types, i.e. Dual() or Num() functions in order to convert initial value into a numeric one. I just cannot see what you source data and local settings are, on the other hand QV have to parse dates and process them properly.

Best regards,

Maxim

Not applicable
Author

So it seems I was able to fix this problem. I had to do this by first grouping together the field and then sort. Thanks for all your help though

TMP:

NoConcatenate load DISTINCT WEEK_START AS StartDT, WEEK_END as EndDT, count(1)

Resident IQNT_TRAFFIC

Group by WEEK_START, WEEK_END

Order BY WEEK_START desc;