Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to split column in to two columns

Hi,

I have Address column like below

32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP

How can I get 32 Beach Drive, Scratby, Great mouth, folk, UK in one column and UY29 3NP in another column.

can anyone help me please.

Thanks.

37 Replies
Not applicable
Author

Hi,  Thanks,

Could you please help me as I am trying to get the Address as parts

I,e could I get te second part and third part separately,

i,e Scartby as one column

Great nouth as another column .

Please help me this is the one I need to sort out please.

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Hi Amelia,

probably the easiest solution is to use SubField.

For example SubField(Address, ',' 1) will retrieve the first part, SubField(Address, ',' 2) the second, etc...

You may wish to include an If condition around each so that if the subfield doesn't exist it doesn't error.

e.g.

If (SubstringCount(Address,',')>=1, SubField(Address, ',' 1), Null()) as FirstPart,

If (SubstringCount(Address,',')>=2, SubField(Address, ',' 2), Null()) as SecondPart,

etc...

Marcus

rustyfishbones
Master II
Master II

Amelia,

Just say SUBFIELD(Address,',',) to get them all separted

Not applicable
Author

Hi,

thanks.

I tried this but it is showing error at '1' place.

Please help me how to set this as I am really struggling this to get this done since days.

Not applicable
Author

Thanks and I used the SUBFIELD and it showing values incorrect order
Is there any other way to get this. Please this becomes now as big task for me. please help.

marcus_malinow
Partner - Specialist III
Partner - Specialist III


Aah, a couple of missing commas

If (SubstringCount(Address,',')>=1, SubField(Address, ',', 1), Null()) as FirstPart,

If (SubstringCount(Address,',')>=2, SubField(Address, ',', 2), Null()) as SecondPart,

Not applicable
Author

Thanks and I used the same and it's showing data when there is no data in second place. I need to have blank values if there is no data and values if there is data exists.

Please could help me how to set this.

rustyfishbones
Master II
Master II

If there are blank values or no data, these need to be separated by a comma, otherwise it won't work.

so if the data is like

32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP (this will be fine)


if it's like this


32 Beach Drive, , , , UK, UY29 3NP (it will also be fine)


however, if other addresses are like this


32 Beach Drive,folk, UK, UY29 3NP (it will not be fine)


you need to understand the Address field data first before you decide what function will be required to return the correct result




Not applicable
Author

Thanks and yes you are right I have most of the data as below

32 Beach Drive,folk, UK, UY29 3NP

so, in thus case how can I set this with out any issues. please help me.

rustyfishbones
Master II
Master II

Where is the Source Data coming from?

Excel, Access, or a Database?