check and load if text contains several values and load them seperatly

[#1]

Hello,

I have several values in one field in an excel sheet.

Example:

4 Sites are part of Publisher 1. They are only seperated by a komma.

Publisher	Publisher ID	Sites
Publisher 1	12345	Site 1, Site 2, Site 3, Site 4
Publisher 2	67890	Site 5, Site 6, Site 7

Now I want Qlikview to check and load all sites seperatly only under the premise that they are still assigned to their publisher and publisher ID.

Like so:

Publisher ID

Publisher	Publisher ID	Sites
Publisher 1	12345	Site 1
Publisher 1	12345	Site 2
Publisher 1	12345	Site 3
Publisher 1	12345	Site 4
Publisher 2	67890	Site 1
Publisher 2	67890	Site 2
Publisher 2	.....	.....

Is there any possibility in doing that?

Best

Philipp

[vishsaggi · Accepted solution]

Using subfield function try this?

LOAD *, SubField(Sites, ',') AS SitesSep;

LOAD Publisher,

     PublisherID,

     Sites

FROM

[..\Desktop\samp.xls]

(biff, embedded labels, table is Sheet3$);

[MarcoWedel]

or without preceding load:

LOAD Publisher,
    [Publisher ID],
    Trim(SubField(Sites,',')) as Sites
FROM [https://community.qlik.com/thread/248501] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

[#4]

Thanks!

It did work!

Best

Philipp