topic Re: Creating new rows & splitting up text strings in QlikView

Creating new rows & splitting up text strings

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

Hello Experts!

Here is how my marketing campaign table looks like now. I am simplifying for the sake of discussion. Each unique email that is used by marketing campaigns is listed once and the campaigns utilizing that email are listed in one cell separated by a semi-colon (see below).

Campaign name	lead Email
Campaign A; Campaign B; Campaign C	aaffqa#@gmail.com

Here is how I want this to look so I can more easily manipulate the data in the future:

Campaign name	Email
Campaign A	aaffqa#@gmail.com
Campaign B	aaffqa#@gmail.com
Campaign C	aaffqa#@gmail.com

How do I do this? I have no idea.

Re: Creating new rows & splitting up text strings

sunny_talwar — Mon, 06 Feb 2017 20:08:45 GMT

Try this:

LOAD SubField([Campaign name], '; ') as [Campaign name],

[lead Email] as Email

FROM ....

Re: Creating new rows & splitting up text strings

maxgro — Mon, 06 Feb 2017 20:10:12 GMT

with subfield (replace with your field names)

load SubField(f1, ';') as f11, f2 Inline [

f1, f2

Campaign A; Campaign B; Campaign C , aaffqa#@gmail.com

];

Re: Creating new rows & splitting up text strings

sunny_talwar — Mon, 06 Feb 2017 20:11:37 GMT

Sample script:

Table:

LOAD SubField([Campaign name], '; ') as [Campaign name],

[lead Email] as Email;

LOAD * INLINE [

Campaign name, lead Email

Campaign A; Campaign B; Campaign C, aaffqa#@gmail.com

];