Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good afternoon!
I have a problem and I do not know how to solve
I need to show how many customers returned to the store in less than 24/48/72 hours.
I have the following table:
ID | DATE | CUSTOMERS |
6223875 | 03/02/2018 19:18:37 | 1050943 |
6223876 | 31/01/2018 19:18:37 | 1050943 |
6223874 | 24/01/2018 19:18:37 | 1050943 |
6220698 | 24/01/2018 07:46:19 | 1050943 |
6220443 | 23/01/2018 03:41:50 | 1050943 |
5813877 | 21/01/2018 02:58:47 | 1050943 |
If I select the date 03/02/2018 I need that my client appears sorted with the return in 72 hours
If I select the date of 01/24/2018 I need my client to appear ranked with the return in 24 hours between 23/01/2018 and 01/24/2018
If I select the date 23/01/2018 I need my client to appear ranked with the return in 48 hours
If I select the date of 01/21/2018 my client will not be considered
How can I do this?
I've already tried using Peek, Below, above, interval, and i tried to create a table on script.
Thanks
Load script like this should do the trick:
Temp:
LOAD ID,
DATE#(DATE, 'DD/MM/YYY hh:mm:ss') AS DATE,
CUSTOMERS
INLINE [
ID, DATE, CUSTOMERS
6223875, 03/02/2018 19:18:37, 1050943
6223876, 31/01/2018 19:18:37, 1050943
6223874, 24/01/2018 19:18:37, 1050943
6220698, 24/01/2018 07:46:19, 1050943
6220443, 23/01/2018 03:41:50, 1050943
5813877, 21/01/2018 02:58:47, 1050943
];
Final:
LOAD ID,
DATE,
CUSTOMERS,
IF(DIFF <= 1, 24, IF(DIFF <= 2, 48, IF(DIFF <= 3, 72))) AS RETURN_WITHIN;
LOAD ID,
DATE,
CUSTOMERS,
IF(CUSTOMERS = PREVIOUS(CUSTOMERS), DATE#(DATE, 'DD/MM/YYY hh:mm:ss') - DATE#(PREVIOUS(DATE), 'DD/MM/YYY hh:mm:ss')) AS DIFF
RESIDENT Temp
ORDER BY CUSTOMERS, DATE;
DROP TABLE Temp;
It will return a table that looks like this:
ID | DATE | CUSTOMERS | RETURN_WITHIN |
---|---|---|---|
5813877 | 1/21/2018 2:58:47 AM | 1050943 | |
6220443 | 1/23/2018 3:41:50 AM | 1050943 | 72 |
6220698 | 1/24/2018 7:46:19 AM | 1050943 | 48 |
6223874 | 1/24/2018 7:18:37 PM | 1050943 | 24 |
6223876 | 1/31/2018 7:18:37 PM | 1050943 | |
6223875 | 2/3/2018 7:18:37 PM | 1050943 | 72 |
Load script like this should do the trick:
Temp:
LOAD ID,
DATE#(DATE, 'DD/MM/YYY hh:mm:ss') AS DATE,
CUSTOMERS
INLINE [
ID, DATE, CUSTOMERS
6223875, 03/02/2018 19:18:37, 1050943
6223876, 31/01/2018 19:18:37, 1050943
6223874, 24/01/2018 19:18:37, 1050943
6220698, 24/01/2018 07:46:19, 1050943
6220443, 23/01/2018 03:41:50, 1050943
5813877, 21/01/2018 02:58:47, 1050943
];
Final:
LOAD ID,
DATE,
CUSTOMERS,
IF(DIFF <= 1, 24, IF(DIFF <= 2, 48, IF(DIFF <= 3, 72))) AS RETURN_WITHIN;
LOAD ID,
DATE,
CUSTOMERS,
IF(CUSTOMERS = PREVIOUS(CUSTOMERS), DATE#(DATE, 'DD/MM/YYY hh:mm:ss') - DATE#(PREVIOUS(DATE), 'DD/MM/YYY hh:mm:ss')) AS DIFF
RESIDENT Temp
ORDER BY CUSTOMERS, DATE;
DROP TABLE Temp;
It will return a table that looks like this:
ID | DATE | CUSTOMERS | RETURN_WITHIN |
---|---|---|---|
5813877 | 1/21/2018 2:58:47 AM | 1050943 | |
6220443 | 1/23/2018 3:41:50 AM | 1050943 | 72 |
6220698 | 1/24/2018 7:46:19 AM | 1050943 | 48 |
6223874 | 1/24/2018 7:18:37 PM | 1050943 | 24 |
6223876 | 1/31/2018 7:18:37 PM | 1050943 | |
6223875 | 2/3/2018 7:18:37 PM | 1050943 | 72 |