Convert record in range to row for excel

[boonhaw_tan]

Hi All,

I got an excel file where the scenario ID is in range format and i want to convert it to unique scenario ID as below:-

Any suggestion method to extract it?

[sunny_talwar]

What is the logic behind creating the New Scenario ID? create 2 more for each Old Scenario ID?

[boonhaw_tan]

Hi Sunny,

Thank a lot for your response.

In old scenario ID the digit part are in number range.

e.g: UAT_FM_0025-0027 where 0025-0027 is in range between 0025 and 0027 

Thus, i need to help to sperate old scenario ID into unique scenario ID like UAT_FM_0025, UAT_FM_0026 and UAT_FM_0027.

Thanks,

Boon Haw

[sunny_talwar]

Try this:

Table:

LOAD SubField([Old Scenario ID], '_', 1) & '_' & SubField([Old Scenario ID], '_', 2) & Num(Start + IterNo() - 1, '0000') as [New Scenario ID],

  [Old Scenario ID]

While Start + IterNo() - 1 <= End;

LOAD *,

  Num#(SubField(SubField([Old Scenario ID], '_', 3), '-', 1)) as Start,

  Num#(SubField(SubField([Old Scenario ID], '_', 3), '-', 2)) as End;

LOAD * Inline [

Old Scenario ID

UAT_FM_0025-0027

UAT_AR_0010-0013

UAT_AP_0031-0032

];

[boonhaw_tan]

Hi Sunny, It work perfectly.

Thanks for your great!!!

[sunny_talwar]

Awesome, I would request you to close this thread by marking correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny