Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to split address?

Hi,

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

Add1

Street,

City

County

Country

Postcode

Thanks.

36 Replies
Not applicable
Author

Please see attached.

giakoum
Partner - Master II
Partner - Master II

This is a typical delimited file. use the wizard to open it in QV, it will automatically see the fields. The only problem is if comma is also used in the text fields, which for your sake, I hope not.

Not applicable
Author

Thanks. I just attached sample. But actually the data is coming from SQL table. will it work?

ThornOfCrowns
Specialist II
Specialist II

You might need to pad your data to ensure you've got the right data in the right field. I'm also hoping you just typed that text in as an example, not an actual extract of data!

giakoum
Partner - Master II
Partner - Master II

no, it will not.

is the data coming as one single string? So each line in the text file is actually one field value?

its_anandrjs

Your attached sample is tab delimited file when you read this file in qlikview then you get separate columns not need to separate the columns. But check data is correct in the file then you get correct columns.

Hope this helps

its_anandrjs

If data is in single column then use Subfield function to extract the address like

=SubField(Add,1)

=SubField(Add,2)

Hope this helps

Not applicable
Author

If these comma separated values are stored in a single SQL field, you could use SUBFIELD()  to separate them. For example:

LOAD

     SUBFIELD([FieldName],',',1) AS ADD1,

     SUBFIELD([FieldName],',',2) AS Street,

     SUBFIELD([FieldName],',',3) AS City,

     SUBFIELD([FieldName],',',4) AS County,

     SUBFIELD([FieldName],',',5) AS Country,

     SUBFIELD([FieldName],',',6) AS Postcode;

SQL SELECT

     [Fieldname]

FROM TABLE;

Not applicable
Author

That's how I have data. Is there any other solution? Please help me