Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have the folllowing set of data
OPERATOR DATE
Andrew 01-DEC-17 10:40:17
Andrew 01-DEC-17 10:41:36
Andrew 01-DEC-17 10:47:21
Andrew 01-DEC-17 10:48:02
Nicole 01-DEC-17 10:51:05
Nicole 01-DEC-17 10:51:10
Andrew 02-DEC-17 10:51:15
Andrew 02-DEC-17 10:54:10
Sophie 02-DEC-17 10:55:40
Sophie 02-DEC-17 10:57:21
I would like to add a column with a progressive number based on the two fields above; the result should be like this:
OPERATOR DATE PROGRESSIVE
Andrew 01-DEC-17 10:40:17 1
Andrew 01-DEC-17 10:41:36 2
Andrew 01-DEC-17 10:47:21 3
Andrew 01-DEC-17 10:48:02 4
Nicole 01-DEC-17 10:51:05 1
Nicole 01-DEC-17 10:51:10 2
Andrew 02-DEC-17 10:51:15 1
Andrew 02-DEC-17 10:54:10 2
Sophie 02-DEC-17 10:55:40 1
Sophie 02-DEC-17 10:57:21 2
Thank you in advance
You'll need to create a field that only contains the date (without the time). Lets call that one JUSTTHEDATE. Then this one will do the job:
AutoNumber(DATE, OPERATOR & JUSTTHEDATE) AS PROGRESSIVE
You can create the JUSTTHEDATE value by using an expression like this:
Date(Floor(DATE)) AS JUSTTHEDATE
Script or User Interface?
How does your preferred output change if we insert an entry like this in the correct spot based on the DateTime value?
Andrew 01-DEC-17 16:22:33
Script!
It will be like this:
OPERATOR DATE PROGRESSIVE
Andrew 01-DEC-17 10:40:17 1
Andrew 01-DEC-17 10:41:36 2
Andrew 01-DEC-17 10:47:21 3
Andrew 01-DEC-17 10:48:02 4
Andrew 01-DEC-17 16:22:33 5
Try like:
Autonumber(DATE, OPERATOR) as PROGRESSIVE
I get this output:
OPERATOR DATE PROGRESSIVE
Andrew 01-DEC-17 10:40:17 1
Andrew 01-DEC-17 10:41:36 2
Andrew 01-DEC-17 10:47:21 3
Andrew 01-DEC-17 10:48:02 4
Andrew 02-DEC-17 10:51:15 5
Andrew 02-DEC-17 10:54:10 6
Nicole 01-DEC-17 10:51:05 1
Nicole 01-DEC-17 10:51:10 2
Sophie 02-DEC-17 10:55:40 1
Sophie 02-DEC-17 10:57:21 2
It' s not correct because the two rows in bould should be
Andrew 02-DEC-17 10:51:15 1
Andrew 02-DEC-17 10:54:10 2
You'll need to create a field that only contains the date (without the time). Lets call that one JUSTTHEDATE. Then this one will do the job:
AutoNumber(DATE, OPERATOR & JUSTTHEDATE) AS PROGRESSIVE
You can create the JUSTTHEDATE value by using an expression like this:
Date(Floor(DATE)) AS JUSTTHEDATE
Why? Because they are in different dates? If so, you might have to create a Date field separately without timestamp like:
load
*,
AutoNumber(DATE, OPERATOR&DateNew) as Prog;
Load
*,
Date(Floor(Date#(DATE,'DD-MMM-YY hh:mm:ss'))) As DateNew
Inline [
OPERATOR, DATE, PROGRESSIVE
Andrew, 01-DEC-17 10:40:17 , 1
Andrew, 01-DEC-17 10:41:36 , 2
Andrew, 01-DEC-17 10:47:21 , 3
Andrew, 01-DEC-17 10:48:02 , 4
Andrew, 02-DEC-17 10:51:15 , 5
Andrew, 02-DEC-17 10:54:10 , 6
Nicole, 01-DEC-17 10:51:05, 1
Nicole, 01-DEC-17 10:51:10, 2
Sophie, 02-DEC-17 10:55:40, 1
Sophie, 02-DEC-17 10:57:21, 2
]
Can you help me calculating the lead time for each activities?
The output should be like this:
OPERATOR | DATE | LT | PROGRESSIVE | JUSTTHEDATE |
Andrew | 01-DEC-17 10:40:17 | - | 1 | 01-12-17 |
Andrew | 01-DEC-17 10:41:36 | 00:01:19 | 2 | 01-12-17 |
Andrew | 01-DEC-17 10:47:21 | 00:05:45 | 3 | 01-12-17 |
Andrew | 01-DEC-17 10:48:02 | 00:00:41 | 4 | 01-12-17 |
Andrew | 02-DEC-17 10:51:15 | - | 1 | 02-12-17 |
Andrew | 02-DEC-17 10:54:10 | 00:02:55 | 2 | 02-12-17 |
Nicole | 01-DEC-17 10:51:05 | - | 1 | 01-12-17 |
Nicole | 01-DEC-17 10:51:10 | 00:00:05 | 2 | 01-12-17 |
Sophie | 02-DEC-17 10:55:40 | - | 1 | 02-12-17 |
Sophie | 02-DEC-17 10:57:21 | 00:01:41 | 2 | 02-12-17 |
Attatched you can find my attempt; it's not working because i get this
OPERATOR | DATE | Date | PROGRESSIVE | JUSTTHEDATE |
Andrew | 01-DEC-17 10:40:17 | - | 1 | 01-12-17 |
Andrew | 01-DEC-17 10:41:36 | 00:01:19 | 2 | 01-12-17 |
Andrew | 01-DEC-17 10:47:21 | 00:05:45 | 3 | 01-12-17 |
Andrew | 01-DEC-17 10:48:02 | 00:00:41 | 4 | 01-12-17 |
Andrew | 02-DEC-17 10:51:15 | 00:03:13 | 1 | 02-12-17 |
Andrew | 02-DEC-17 10:54:10 | 00:02:55 | 2 | 02-12-17 |
Nicole | 01-DEC-17 10:51:05 | - | 1 | 01-12-17 |
Nicole | 01-DEC-17 10:51:10 | 00:00:05 | 2 | 01-12-17 |
Sophie | 02-DEC-17 10:55:40 | - | 1 | 02-12-17 |
Sophie | 02-DEC-17 10:57:21 | 00:01:41 | 2 | 02-12-17 |
In the fifth row it's calculating the time between two different dates.