How to split column in to two columns

[#1]

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.

[#2]

The source is from SQL Database

[rustyfishbones]

Well then there is not much you can do if there is no comma separating the values.

Maybe someone else can help

Sorry

[marcus_malinow]

Amelia,

I've just tested this, and you'll need to make a small adjustment

Subtract 1 from the SubStringCount check

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

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

Marcus

[#5]

Hi, thanks.

Still it is showing the same problem as in Blanks it is showing data and in data it is showing zeros.

Please help me how to set this as I don't have choice to change source data. so, I should rely with in dashboard itself

[marcus_malinow]

Hi Amelia,

not sure why you're having problems here. Here's an example script I've been playing with using the expressions above, and it seems to work ok.

LOAD 
Address,
SubstringCount(Address,',') as SubStringCount,
If (SubstringCount(Address,',')>=1, SubField(Address, ',', 1), Null()) as Part1,
If (SubstringCount(Address,',')>=1, SubField(Address, ',', 2), Null()) as Part2,
If (SubstringCount(Address,',')>=2, SubField(Address, ',', 3), Null()) as Part3,
If (SubstringCount(Address,',')>=3, SubField(Address, ',', 4), Null()) as Part4,
If (SubstringCount(Address,',')>=4, SubField(Address, ',', 5), Null()) as Part5,
If (SubstringCount(Address,',')>=5, SubField(Address, ',', 6), Null()) as Part6,
If (SubstringCount(Address,',')>=6, SubField(Address, ',', 7), Null()) as Part7,
If (SubstringCount(Address,',')>=7, SubField(Address, ',', 8), Null()) as Part8,
If (SubstringCount(Address,',')>=8, SubField(Address, ',', 9), Null()) as Part9
;
LOAD * INLINE [ 
Address
"32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP" 
"42 Beach Drive, Scratby, Great mouth, folk, aaa, UK, vvvv"
"52 Beach Drive, Scratby, Great mouth, folk, bbb, ccc, UK, kkkk"
"62 Beach Drive, Scratby, Great mouth, folk, UK, zzzz"
"32 Beach Drive,folk, UK, UY29 3NP"
""
];  

[#7]

Thanks for your time. Now I will explain about the problem please find below.

I need to have if Scartby is present it should appear in second column. If Scartby is not present it shouls show as Blank and Folk should appear under Town

[marcus_malinow]

Ok, well we could try using SubField in a slightly different way.

If you use the expression SubField(Address, ',', -1) you'll get the last element (e.g. postcode)

SubField(Address, ',', -2) will give you the country

SubField(Address, ',', -3) will give you the town

[#9]

Thanks and suppose if I want to split the below in to

Address1, Address2,Address3, Town,County,PostCode

32 Beach Drive,folk, UK, UY29 3NP

i,e I have to show Address2 and Address3 as Blanks.

How can I do this. Please help me Please.

[marcus_malinow]

ok, how about these expressions:

if (SubstringCount(Address,',')>=3, SubField(Address, ',', 1), Null()) as address1,
If (SubstringCount(Address,',')>=4, SubField(Address, ',', 2), Null()) as address2,
If (SubstringCount(Address,',')>=5, SubField(Address, ',', 3), Null()) as address3,
SubField(Address, ',', -3) as town,
SubField(Address, ',', -2) as country,
SubField(Address, ',', -1) as postcode

[#11]

Hi, Thanks and some values are showing correct but now it is showing some problem with last three fields.

I Have under Address1 = 18 Gravery street

                   Town= Gavypool

But it is showing Town= 18 Gravery street

                         Country= Gavypool

Please could help as I suppose we are near to destination.