How to split single cell value in to multiple cell values in straight table

[SatyaPaleti]

Hi Folks,

How to split single cell value into multiple cell values in Straight table.

In the above column 'Link' we have 3 multiple links I want to show 3 multiple links in 3 cells. like Google www.google.com in one cell, Fcaebook www.facebook.com in one cell & Amazon www.amazon.com in one cell. Could you please help me to resolve this Issue

Please find attched documents for data & qvw

Thanks and Regards,

Satya

[zhadrakas]

maybe this

*note that this only works if the Domain ending is always '.com'

LOAD Customer,
Duration,
Country,
Link,
if(index(Link,'www.')>=3,subfield(Link, '.com',1) &'.com' , subfield(Link, chr(10),1 )) as Link1,
if(index(Link,'www.')>=3,subfield(Link, '.com',2) &'.com' , subfield(Link, chr(10),2 )) as Link2,
if(index(Link,'www.')>=3,subfield(Link, '.com',3) &'.com' , subfield(Link, chr(10),3 )) as Link3
FROM

(biff, embedded labels, table is [Sheet1$]);

regards

tim

[mdmukramali]

Hi,

You can try something like:

LOAD Customer,
    Duration,
    Country,
    Capitalize(SubField(Rtrim(Ltrim(Replace(Replace(Replace(Replace(Link,chr(10),''),'www.google.com',''),'www.facebook.com',''),'Www.amazon.com',''))),' ')) as Website
FROM

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


LOAD * Inline
[
Website,Link
Google,www.google.com
Facebook,www.facebook.com
Amazon,www.amazon.com
Flipkart,www.flipkart.com
]
;

[SatyaPaleti]

Thank you so  much for your response. Is there any alternate way?

[SatyaPaleti]

Thank you Mohammed I will try it once

[zhadrakas]

it would be great if you could reformat your Excel to a Format which is always the same.

For Example: Always 3 Values in one cell

NOT LIKE THIS

Google www.google.com Facebook
www.facebook.com Amazon
Www.amazon.com

INSTEAD DO IT LIKE THIS

www.google.com
www.facebook.com  
Www.amazon.com

this way you could use this script.

LOAD Customer,
Duration,
Country,
Link,
if(subfield(Link, chr(10),1 )) as Link1,
if(subfield(Link, chr(10),2 )) as Link2,
if(subfield(Link, chr(10),3 )) as Link3
FROM

(biff, embedded labels, table is [Sheet1$]);