How to split address?

[#1]

Hi,

Can anyone help me how to split the address to below columns?

Add1

Street,

City

County

Country

Postcode

Thanks.

[arjunkrishnan]

If U Load Data In QlikView Ex.FileName(Add.txt). To Separate The Value in Using Delimiter (Select Option Comma)->(In File Wizard)..The Values Are Separated By One By One Individual Column ...

Ex . I snd U AttachMent For Letsz Pls Look it Up

[nizamsha]

After reterving the query in qlikview u can take a column one by using

subfield(Address,',',1) as Address1,

subfield(Address,',',1) as Address2,

subfield(Address,',',1) as Address3,

subfield(Address,',',1) as Address4,

like this u can take

[mangalsk]

Data:

LOAD addr

FROM

addr.xlsx

(ooxml, embedded labels, table is Sheet1);

Data1:

LOAD

SubField(addr,',',1) as add1,

SubField(addr,',',2) as street,

SubField(addr,',',3) as City,

SubField(addr,',',4) as Country,

SubField(addr,',',5) as Postcode,

Resident Data;

[ThornOfCrowns]

Your data sample shows that you do not have data in every field, so when you try to split it, it will fail. You will need to cleanse your data if you want to use the solutions given above.

As I said, if that sample is your actual data, you have more to worry about than string slicing.

[#6]

Thanks I tried this. It is showing completely blank straight table. Any help please ?

[#7]

Hi,

Try this to remove additional spaces

Test1:

LOAD @1 as ADD
FROM
C:\Add.xls
(biff, no labels, table is [Sheet1$]);


ResTest:
Load *,
SubField(ADD,', ',1) as ADD1,
SubField(ADD,', ',2) as ADD2,
SubField(ADD,', ',3) as ADD3,
SubField(ADD,', ',4) as ADD4,
SubField(ADD,', ',5) as ADD5,
SubField(ADD,', ',6) as ADD6
Resident Test1;

DROP Table Test1;

[ThornOfCrowns]

You might want to post an extract of your actual Excel file to illustrate the problem more clearly, as it sems that everyone is shooting in the dark, expecting your data to be correct to fit the solution.

[mangalsk]

Hello,

PFA

[#10]

Thanks. When I am reloading it is showing 'field @1 not found'. Please help me how to execute this.

[#11]

Thanks. what I posted is actual exact data.