topic Re: Convert record in range to row for excel in QlikView

Convert record in range to row for excel

boonhaw_tan — Wed, 25 Nov 2020 16:16:04 GMT

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 — Mon, 17 Oct 2016 16:58:22 GMT

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

boonhaw_tan — Tue, 18 Oct 2016 01:35:55 GMT

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 — Tue, 18 Oct 2016 02:58:43 GMT

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 — Tue, 18 Oct 2016 09:03:06 GMT

Hi Sunny, It work perfectly.

Thanks for your great!!!

sunny_talwar — Tue, 18 Oct 2016 16:25:03 GMT

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

Best,

Sunny