Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Mattia
Contributor II

How to cover gaps in numerical sequences?

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

Labels (3)
2 Replies
Highlighted
Partner
Partner

Re: How to cover gaps in numerical sequences?

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
;

Highlighted
Mattia
Contributor II

Re: How to cover gaps in numerical sequences?

Hi,

thanks for the prompt reply.

I try and i let you know ASAP!

Mattia