Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

andremarti17
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

Thank you in advance

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

View solution in original post

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

andremarti17
New Contributor III

Re: Add a counter based on two fields

Script!

andremarti17
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
MVP

Re: Add a counter based on two fields

Try like:

Autonumber(DATE, OPERATOR) as PROGRESSIVE

andremarti17
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

View solution in original post

MVP
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:

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

  ]

andremarti17
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:

OPERATORDATELTPROGRESSIVEJUSTTHEDATE
Andrew01-DEC-17 10:40:17-101-12-17
Andrew01-DEC-17 10:41:3600:01:19201-12-17
Andrew01-DEC-17 10:47:2100:05:45301-12-17
Andrew01-DEC-17 10:48:0200:00:41401-12-17
Andrew02-DEC-17 10:51:15-102-12-17
Andrew02-DEC-17 10:54:1000:02:55202-12-17
Nicole01-DEC-17 10:51:05-101-12-17
Nicole01-DEC-17 10:51:1000:00:05201-12-17
Sophie02-DEC-17 10:55:40-102-12-17
Sophie02-DEC-17 10:57:2100:01:41202-12-17

Attatched you can find my attempt; it's not working because i get this

  

OPERATORDATEDatePROGRESSIVEJUSTTHEDATE
Andrew01-DEC-17 10:40:17-101-12-17
Andrew01-DEC-17 10:41:3600:01:19201-12-17
Andrew01-DEC-17 10:47:2100:05:45301-12-17
Andrew01-DEC-17 10:48:0200:00:41401-12-17
Andrew02-DEC-17 10:51:1500:03:13102-12-17
Andrew02-DEC-17 10:54:1000:02:55202-12-17
Nicole01-DEC-17 10:51:05-101-12-17
Nicole01-DEC-17 10:51:1000:00:05201-12-17
Sophie02-DEC-17 10:55:40-102-12-17
Sophie02-DEC-17 10:57:2100:01:41202-12-17

In the fifth row it's calculating the time between two different dates.