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
COMPANY_CODE DATE STORE HOUR RETAIL INVOICES CUSTOMERS
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.
COMPANY_CODE DATE STORE HOUR RETAIL INVOICES CUSTOMERS
01 30/08/2017 101 08 1.180 250 15
Some times we have the same line many times like the following example
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
01 30/08/2017 101 08:15 1.180 250 15
01 30/08/2017 101 08:15 1.180 250 15
My question is how can i have only the last record per HOUR ?
Thank you in advance.
how could you recognize the "last record"?
IS there a unique ID that will be counted up or are the rows in correct order in your database?
For your question you can manage your fields as my script. Note - This script return Maximum hour from the table
Sample:
Load @1, @2, Hour From table
Final:
NoConcatenate
Load Max(Hour) as Hour
Resident Sample
Group By @1, @2 Order By @1;
Drop Table Sample;
try below
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,
max(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'),STORE;
Hi Gou,
After loading your data try something like:
DistinctData:
LOAD
DISTINCT
COMPANY_CODE,
CUSTOMERS,
INVOICES,
RETAIL,
DATE,
HOUR,
Hour(time#(HOUR,'HH:MM')) as HourOfDay,
STORE;
From YourTableName;
Drop Table YourTableName;
Inner Join
Load
Max(HOUR) as HOUR,
HourOfDay,
DATE
Resident DistinctData
Group by HourOfDay, DATE;
Good luck
Andrew
Hello Andrew
If you load the following data
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
01,30/08/2017,101,08:15,1.180,250,15
01,30/08/2017,101,08:15,1.180,250,15
01,30/08/2017,101,09:05,2.000,300,20
01,30/08/2017,101,09:10,2.200,310,25
01,30/08/2017,101,09:15,2.380,400,30
01,30/08/2017,101,09:25,2.480,350,30
01,30/08/2017,101,09:40,2.400,380,35
01,30/08/2017,101,09:40,2.400,380,35
and you run your script you will have the following results
01 | 30/08/2017 | 101 | 08:10 | 1.200 | 250 | 15 | 8 |
01 | 30/08/2017 | 101 | 09:10 | 2.200 | 310 | 25 | 9 |
The correct rows that we will expect to have are
01,30/08/2017,101,08:15,1.180,250,15
01,30/08/2017,101,09:40,2.400,380,35
any idea ?
Hi Gou,
I found the problem. Run it again after replacing
Hour(time#(HOUR,'HH:MM')) as HourOfDay,
with
Hour(time#(HOUR,'hh:mm')) as HourOfDay,
Result:
COMPANY_CODE | DATE | STORE | HOUR | RETAIL | INVOICES | CUSTOMERS |
---|---|---|---|---|---|---|
01 | 30/08/2017 | 101 | 08:15 | 1.180 | 250 | 15 |
01 | 30/08/2017 | 101 | 09:40 | 2.400 | 380 | 35 |
Regards
Andrew
Andrew it is working.
I will try to apply the code to the master data in order to see the results.
I will keep you inform.
Thank you for your help.
Good luck!!
The correct rows that we will expect to have are
01,30/08/2017,101,08:15,1.180,250,15
01,30/08/2017,101,09:40,2.400,380,35
RESULT
SCRIPT
Test:
load * inline [
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
01,30/08/2017,101,08:15,1.180,250,15
01,30/08/2017,101,08:15,1.180,250,15
01,30/08/2017,101,09:05,2.000,300,20
01,30/08/2017,101,09:10,2.200,310,25
01,30/08/2017,101,09:15,2.380,400,30
01,30/08/2017,101,09:25,2.480,350,30
01,30/08/2017,101,09:40,2.400,380,35
01,30/08/2017,101,09:40,2.400,380,35
];
Test2:
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;
DROP TABLE Test;