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!
