topic Re: Text To Columns in QlikView

Text To Columns

Wed, 23 Apr 2014 01:55:04 GMT

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

Re: Text To Columns

jagan — Wed, 23 Apr 2014 02:06:13 GMT

Hi,

Try like this

Data:

Load

UniqueID,

SubField(Guests, ',') AS Guests

FROM DataSource;

Regards,

Jagan.

Re: Text To Columns

Wed, 23 Apr 2014 02:54:21 GMT

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.

Re: Text To Columns

jagan — Wed, 23 Apr 2014 03:10:46 GMT

Hi,

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

Regards,

Jagan.

Re: Text To Columns

Wed, 23 Apr 2014 03:19:26 GMT

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