Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

PublisherPublisher IDSites
Publisher 112345Site 1, Site 2, Site 3, Site 4
Publisher 267890Site 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 112345Site 1
Publisher 112345Site 2
Publisher 112345Site 3
Publisher 112345Site 4
Publisher 267890Site 1
Publisher 267890Site 2
Publisher 2..........

Is there any possibility in doing that?

Best

Philipp

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

Using subfield function try this?

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

LOAD Publisher,

     PublisherID,

     Sites

FROM

[..\Desktop\samp.xls]

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

View solution in original post

3 Replies
vishsaggi
Champion III
Champion III

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:

QlikCommunity_Thread_248501_Pic1.JPG

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

Not applicable
Author

Thanks!

It did work!

Best

Philipp