Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Text To Columns

I have a column of data with values separated by commas.   I have  few tricks up my sleeve but they require a too many temp resident loads.  I was hoping to find the most optimized solution for this type of conversion:

Source table:

Unique ID        Guests

1                     Joe Smith, Amy Lee, John Deer

2                     Amy Lee, Jane Doe

3                    Jerry Jones, John Deer, Joe Smith

4                    Jack Rabbit, Cecil Williams

Destination Table:

I want to load it as

ID                Guest

1                  Joe Smith

1                  Amy Lee

1                  John Deer

2                  Amy Lee

2                  Jane Doe

3                  Jerry Jones

3                  John Deer

3                  Joe Smith

4                  Jack Rabbit

4                  Cecil Williams

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

Data:

Load

UniqueID,

SubField(Guests, ',') AS Guests

FROM DataSource;

Regards,

Jagan.

View solution in original post

4 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

Data:

Load

UniqueID,

SubField(Guests, ',') AS Guests

FROM DataSource;

Regards,

Jagan.

Not applicable
Author

Thank you!  That worked perfectly as long as run it before I load the fact table.  for some reason after I load the fact table it only gives me the last Guest following the last comma. 

jagan
Luminary Alumni
Luminary Alumni

Hi,

It is difficult to tell the issue, can you attach some sample values and sample file.

Regards,

Jagan.

Not applicable
Author

Thank you so much for your help.  It may be the way I was loading my fact table.  But moving it to the initial load and creating the Guest column from the original QVD instead of the resident table, made it work.  I am all set now.  I am sure it must have been the fact table.  I am cleaning that up now.

You solved my problem.  thank you very much