Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i have the following script
LOAD COMPANY_CODE,
CUSTOMERS,
INVOICES,
RETAIL,
DATE,
HOUR,
STORE;
SQL SELECT COMPANY_CODE,
MAX(CUSTOMERS) AS CUSTOMERS,
MAX(INVOICES) AS INVOICES,
MAX(RETAIL) AS RETAIL,
TO_CHAR(TRANS_DATE,'DD/MM/YYYY') AS DATE,
SUBSTR(TRANS_TIME,1,2) AS HOUR,
STORE
FROM SALES
WHERE
TO_CHAR(TRANS_DATE,'YYYYMMDD') < TO_CHAR(SYSDATE,'YYYYMMDD') AND
SUBSTR(TRANS_TIME,1,2) BETWEEN '08' AND '21'
GROUP BY COMPANY_CODE,TO_CHAR(TRANS_DATE,'DD/MM/YYYY'), SUBSTR(TRANS_TIME,1,2),STORE;
this brings the max value of CUSTOMERS,INVOICES,RETAIL per COMPANY_CODE,DATE,HOUR (08,09,10 .....) and STORE
the form of the data is like
COMPANY_CODE DATE STORE HOUR RETAIL INVOICES CUSTOMERS
01 30/08/2017 101 08:05 1.000 200 10
01 30/08/2017 101 08:10 1.200 250 15
01 30/08/2017 101 08:15 1.180 250 15
According to the script the data i will have per hour is like
01 30/08/2017 101 08 1.200 250 15
but is not correct because is not the last record. If you see the RETAIL increased because we had refund.
01 30/08/2017 101 08 1.180 250 15
Some times we have the same line many times like the following example
My question is how can i have only the last record per HOUR ?
Thank you in advance.
Hello Massimo,
Very interesting script.
Can you tell me how can i take only the first digits of the hour_f like 08 or 09 ?
Andrew how can i take only the first digits of the hour like 08 or 09 ?
Test2:
LOAD
*,
Hour(HOUR_F);
NoConcatenate load
Time(FLOOR(Time#(HOUR, 'hh:mm'), 1/24)) as HOUR_F
RESIDENT Test
WHERE FLOOR(Time#(HOUR, 'hh:mm'), 1/24) <> FLOOR(Time#(Peek('HOUR'), 'hh:mm'), 1/24)
ORDER BY COMPANY_CODE, STORE, DATE, HOUR desc;
or
Num(Hour(HOUR_F), '00')
Hi Gou,
In my script I used this expression:
Hour(time#(HOUR,'hh:mm'))
This returns the hour part of the time which rather confusingly is called HOUR.
After my script has ran there will be the field HourOfDay, is this not what you need?
Kind regards
Andrew
