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

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

SatyaPaleti — Wed, 25 Nov 2020 16:16:04 GMT

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

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

zhadrakas — Thu, 11 Jan 2018 07:55:45 GMT

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

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

mdmukramali — Thu, 11 Jan 2018 08:09:09 GMT

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
]
;

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

SatyaPaleti — Thu, 11 Jan 2018 08:40:09 GMT

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

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

SatyaPaleti — Thu, 11 Jan 2018 08:40:35 GMT

Thank you Mohammed I will try it once

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

zhadrakas — Thu, 11 Jan 2018 09:15:38 GMT

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$]);