How to cover gaps in numerical sequences?

[Mattia]

Hi guys,

i've the following problem: i need to cover gaps in numerical sequences.

I've two fields, e.g., FIELD_C and FIELD_P:

• FIELD_C: 0000001 have this value as FIELD_P: 1,2,4,7,9
• FIELD_C: 0000002 have this value as FIELD_P: 2,3,5,6

So, i want this output:

• FIELD_C: 0000001 have this value as FIELD_P: 1,2,3,4,5 (1 and 2 is ok, 3 is the new 4, 4 is the new 7 and 5 is the new 9)
• FIELD_C: 0000002 have this value as FIELD_P: 1,2,3,4 (1 is the new 2, 2 is the new 3, 3 is the new 5 and 4 is the new 6)

Please, can you show me the right expression to do this?

Thanks,

Mattia

[StarinieriG]

Hi

probably this could work

NoConcatenate
OUTPUT:
LOAD
FIELD_C,
If(FIELD_C<>Previous(FIELD_C), 1,
If(FIELD_P<>Previous(FIELD_P),(Peek('FIELD_P')+1),Peek('FIELD_P'))) AS FIELD_P,
FIELD_P AS FIELD_P_OLD
Resident ...
Order By
FIELD_C,
FIELD_P
;

[Mattia]

Hi,

thanks for the prompt reply.

I try and i let you know ASAP!

Mattia