Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Text To Columns

Hi,

Try like this

Data:

Load

UniqueID,

SubField(Guests, ',') AS Guests

FROM DataSource;

Regards,

Jagan.

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Text To Columns

Hi,

Try like this

Data:

Load

UniqueID,

SubField(Guests, ',') AS Guests

FROM DataSource;

Regards,

Jagan.

Not applicable

Re: Text To Columns

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. 

MVP & Luminary
MVP & Luminary

Re: Text To Columns

Hi,

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

Regards,

Jagan.

Not applicable

Re: Text To Columns

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