Renumber per (Duplicate)ID

cdss-developer — Tue, 09 Oct 2018 07:39:54 GMT

Hello everyone, I need some help with this.

In this example you can see that I made a DuplicateID, where the Likelihood and the Impact are the same.

ID	Likelihood	Impact	Risk name	DuplicateFlag	DuplicateID
32	3.00	5.00	adf	1	1
33	3.00	5.00	jlkaljkdlkj	1	1
34	2.00	3.00	diekdie	1	2
35	2.00	3.00	ajaja	1	2
36	2.00	4.00	ldio	1	3
37	3.00	4.00	alalae	0
38	2.00	5.00	joj	0
39	3.00	5.00	dd	1	1
40	2.00	4.00	gw	1	3
41	3.00	3.00	ert	0
42	5.00	2.00	djkflskd	1	4
43	4.00	4.00	fd	0
44	2.00	3.00	we	1	2
45	2.00	3.00	as	1	2
46	5.00	2.00	af	1	4
47	3.00	4.20	gh	0
48	2.00	3.00	rr	1	2
49	3.00	4.10	ff	0
50	2.00	4.00	ff	1	3
51	2.00	3.00	gg	1	2

Now, I want to have one extra column that shows each ID being renumbered starting from 1 on every DuplicateID.

Like this (column 'Result'):

ID	Likelihood	Impact	Risk name	DuplicateFlag	DuplicateID	Result
32	3.00	5.00	adf	1	1	1
33	3.00	5.00	jlkaljkdlkj	1	1	2
34	2.00	3.00	diekdie	1	2	1
35	2.00	3.00	ajaja	1	2	2
36	2.00	4.00	ldio	1	3	1
37	3.00	4.00	alalae	0
38	2.00	5.00	joj	0
39	3.00	5.00	dd	1	1	3
40	2.00	4.00	gw	1	3	2
41	3.00	3.00	ert	0
42	5.00	2.00	djkflskd	1	4	1
43	4.00	4.00	fd	0
44	2.00	3.00	we	1	2	3
45	2.00	3.00	as	1	2	4
46	5.00	2.00	af	1	4	2
47	3.00	4.20	gh	0
48	2.00	3.00	rr	1	2	5
49	3.00	4.10	ff	0
50	2.00	4.00	ff	1	3	3
51	2.00	3.00	gg	1	2	6

My code:

Table:

Load * inline [

ID, Likelihood, Impact, Risk name,

32, 3.00, 5.00, adf

33, 3.00, 5.00, jlkaljkdlkj

34, 2.00, 3.00, diekdie

35, 2.00, 3.00, ajaja

36, 2.00, 4.00, ldio !

37, 3.00, 4.00, alalae

38, 2.00, 5.00, joj

39, 3.00, 5.00, dd

40, 2.00, 4.00, gw

41, 3.00, 3.00, ert

42, 5.00, 2.00, djkflskd

43, 4.00, 4.00, fd

44, 2.00, 3.00, we

45, 2.00, 3.00, as

46, 5.00, 2.00, af

47, 3.00, 4.20, gh

48, 2.00, 3.00, rr

49, 3.00, 4.10, ff

50, 2.00, 4.00, ff

51, 2.00, 3.00, gg

];

left join (Table)

load Impact, Likelihood, if(count(Likelihood)>1,1,0) as DuplicateFlag,

if(count(Likelihood)>1,AutoNumber(Impact)) as DuplicateID

Resident Table

group by Impact, Likelihood;

I think it should be very simple (I think). I hope you can help me with this.

Thanks!

Re: Renumber per (Duplicate)ID

lironbaram — Tue, 09 Oct 2018 08:00:25 GMT

Have a look at the attach example

Re: Renumber per (Duplicate)ID

cdss-developer — Tue, 09 Oct 2018 08:08:08 GMT

Yes that's it, thank you very much!
Greetings,

Eelco

topic Re: Renumber per (Duplicate)ID in QlikView

Renumber per (Duplicate)ID

Re: Renumber per (Duplicate)ID

Re: Renumber per (Duplicate)ID