topic How to separate the text value into one by one? in QlikView

How to separate the text value into one by one?

Wed, 25 Nov 2020 16:16:04 GMT

hi all,

I have a address column with comma separated values like The Business Centre,61 Wellfield Road,Roath,Cardiff

Can i separate this values into one by one with comma like,

The Business Centre,

61 Wellfield Road,

Roath,

Cardiff

If possible Can anyone explain how can i do this?

Regards,

Mani

Re: How to separate the text value into one by one?

Anil_Babu_Samineni — Wed, 19 Oct 2016 17:07:18 GMT

Yes, you can

Concat(fieldname,',',chr(10)) as newfield

Re: How to separate the text value into one by one?

sunny_talwar — Wed, 19 Oct 2016 17:09:57 GMT

I think all you need is this

LOAD SubField(FieldName, ',') as NewField

Read here about SubField function:

The Secret Life of SubField

Re: How to separate the text value into one by one?

MarcoWedel — Wed, 19 Oct 2016 22:42:49 GMT

Hi,

if you really want to keep the trailing comma, then one solution could be:

LOAD *,
    Replace(SubField(Replace(Address,',','@comma@,'),','),'@comma@',',') as AddressSubstring
INLINE [
    Address
    "The Business Centre,61 Wellfield Road,Roath,Cardiff"
    "QLIKTECH UK LTD, 1020 Eskdale Road, Winnersh, Wokingham, Berkshire"
];

hope this helps

regards

Marco

Re: How to separate the text value into one by one?

Anonymous — Thu, 20 Oct 2016 05:03:24 GMT

Use replace() and replace comma "," value to get your result .

Thanks,

Re: How to separate the text value into one by one?

Thu, 20 Oct 2016 05:46:46 GMT

Hi Marco Wedel thanks for reply...

This is my expected result but it is not working in text box and this script gets the values in ascending order like,

61 Wellfield Road,

Cardiff

The Business Centre,

Roath,

i want the actual result is showing in text box,,,,

Thanks,

Mani

Re: How to separate the text value into one by one?

MarcoWedel — Thu, 20 Oct 2016 16:39:28 GMT

Replace(Address,',',','& Chr(10))

Regards

Marco