Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'));
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;
Hi,
Let try in first load statement
.....
FROM
[SOURCE.xlsx]
(ooxml, embedded labels, table is Sheet1) ORDER BY WEEK_START DESC;
Regards,
Sokkorn
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
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;