Add a counter based on two fields

[Anonymous]

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

[Peter_Cammaert]

Script or User Interface?

[Peter_Cammaert]

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

[Anonymous]

Script!

[Anonymous]

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

[tresesco]

Try like:

Autonumber(DATE, OPERATOR) as PROGRESSIVE

[Anonymous]

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

[Peter_Cammaert · Accepted solution]

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

[tresesco]

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

  ]

[Anonymous]

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.