# New to QlikView

Discussion board where members can get started with QlikView.

New Contributor III

## Add a counter based on two fields

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

1 Solution

Accepted Solutions

## Re: Add a counter based on two fields

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

10 Replies

## Re: Add a counter based on two fields

Script or User Interface?

## Re: Add a counter based on two fields

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

New Contributor III

## Re: Add a counter based on two fields

Script!

New Contributor III

## Re: Add a counter based on two fields

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

MVP

## Re: Add a counter based on two fields

Try like:

Autonumber(DATE, OPERATOR) as PROGRESSIVE

New Contributor III

## Re: Add a counter based on two fields

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

## Re: Add a counter based on two fields

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

MVP

## Re: Add a counter based on two fields

Why? Because they are in different dates? If so, you might have to create a Date field separately without timestamp like:

*,

AutoNumber(DATE, OPERATOR&DateNew) as Prog;

*,

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

]

New Contributor III

## Re: Add a counter based on two fields

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.