topic Re: How to cover gaps in numerical sequences? in QlikView

How to cover gaps in numerical sequences?

Mattia — Sat, 16 Nov 2024 19:45:26 GMT

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

Re: How to cover gaps in numerical sequences?

StarinieriG — Tue, 05 Nov 2019 14:21:19 GMT

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
;

Re: How to cover gaps in numerical sequences?

Mattia — Tue, 05 Nov 2019 14:55:18 GMT

Hi,

thanks for the prompt reply.

I try and i let you know ASAP!

Mattia