topic Re: Add a counter based on two fields in QlikView

Add a counter based on two fields

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Add a counter based on two fields

Peter_Cammaert — Fri, 16 Feb 2018 08:39:04 GMT

Script or User Interface?

Re: Add a counter based on two fields

Peter_Cammaert — Fri, 16 Feb 2018 08:42:04 GMT

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

Re: Add a counter based on two fields

Anonymous — Fri, 16 Feb 2018 08:45:33 GMT

Script!

Re: Add a counter based on two fields

Anonymous — Fri, 16 Feb 2018 08:48:10 GMT

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

Re: Add a counter based on two fields

tresB — Fri, 16 Feb 2018 08:53:36 GMT

Try like:

Autonumber(DATE, OPERATOR) as PROGRESSIVE

Re: Add a counter based on two fields

Anonymous — Fri, 16 Feb 2018 09:01:25 GMT

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

Peter_Cammaert — Fri, 16 Feb 2018 09:10:14 GMT

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

Re: Add a counter based on two fields

tresB — Fri, 16 Feb 2018 09:22:07 GMT

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

]

Re: Add a counter based on two fields

Anonymous — Fri, 16 Feb 2018 09:30:11 GMT

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.

Re: Add a counter based on two fields

Peter_Cammaert — Fri, 16 Feb 2018 09:57:17 GMT

In your LT calculation, skip all rows with PROGRESSIVE = 1. Those rows mean that either the OPERATOR or the JUSTTHEDATE value has changed.

BTW we prefer that you start a new discussion for every new question.