Create duplicate records for fields with ranges

[#1]

Hi all,

I  have a table with range data and I want to create separate rows for each value int he range.

Example:

Input file:

Field A          Field B

20                    INC1234

30                    INC1239

40                    INC1240-INC1250

Expected result:

Field A          Field B

20                    INC1234

30                    INC1239

40                    INC1240

40                    INC1241

40                    INC1242

40                    INC1243

40                    INC1244

40                    INC1245

40                    INC1246

40                    INC1247

40                    INC1248

40                    INC1249

40                    INC1250

thanks

[maxgro · Accepted solution]

RESULT

SCRIPT

Source:

load

FieldA, FieldB,

replace(SubField(FieldB, '-', 1),'INC','') as From,

if(Index(FieldB,'-')=0, replace(FieldB,'INC',''), replace(SubField(FieldB, '-', 2),'INC','')) as To;

load * inline [

FieldA,          FieldB,

20    ,                INC1234

30    ,                INC1239

40    ,                INC1240-INC1250

];

Table:

load

FieldA,

FieldB,

From,

To,

From + IterNo()-1 as New

Resident Source

while IterNo() <= To - From + 1

;

DROP Table Source;

[#3]

thanks