topic Re: Create duplicate records for fields with ranges in QlikView

Create duplicate records for fields with ranges

Fri, 23 May 2014 17:14:44 GMT

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

Re: Create duplicate records for fields with ranges

maxgro — Fri, 23 May 2014 18:23:15 GMT

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;

Re: Create duplicate records for fields with ranges

Fri, 23 May 2014 19:09:03 GMT

thanks