topic Re: Problem with resident sorting in QlikView

Problem with resident sorting

Fri, 08 Feb 2013 00:11:16 GMT

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'));

Re: Problem with resident sorting

Sokkorn — Fri, 08 Feb 2013 01:47:02 GMT

Hi,

Let try in first load statement

.....

FROM

[SOURCE.xlsx]

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

Regards,

Sokkorn

Re: Problem with resident sorting

maksim_senin — Fri, 08 Feb 2013 02:04:19 GMT

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

Re: Problem with resident sorting

Sat, 09 Feb 2013 19:53:26 GMT

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;