Generate table out of cell containing an intervall with separators ".." and ","

[simonh]

Hi people,

I'm facing a challenge with generating a table out of cells that contains interval with ".." and ","  .

From having this table already loaded...

Description	Totaling
TypeA	1011..1019,1030,1040
TypeB	1220,1230,1240,1250,1260
TypeC	1311..1312

1011..1019 simply means all numbers in between, i.e. 1011,1012,...,1019

1030,1040 means the numbers 1030 and 1040

Based on these, I would like to generate a new table that looks like this:

Description_	Totaling_
TypeA	1011
TypeA	1012
TypeA	1013
...
TypeB	1220
TypeB	1230
...

Anyone has an idea on how to solve this task efficiently in Qlikview load script?

Kind regards

Simon

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD Description,

Min_Totaling + IterNo() - 1 as Totaling

While Min_Totaling + IterNo() - 1 <= Max_Totaling;

LOAD Description,

If(Index(Totaling, '..') > 0, SubField(Totaling, '..', 1), Totaling) as Min_Totaling,

If(Index(Totaling, '..') > 0, SubField(Totaling, '..', 2), Totaling) as Max_Totaling;

LOAD Description,

SubField(Totaling, ',') as Totaling;

LOAD * INLINE [

    Description, Totaling

    TypeA, "1011..1019,1030,1040"

    TypeB, "1220,1230,1240,1250,1260"

    TypeC, 1311..1312

];

[simonh]

Worked like a charm, awesome - thank you!