topic Re: How to split address? in QlikView

How to split address?

Tue, 01 Apr 2014 15:39:54 GMT

Hi,

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

Add1

Street,

City

County

Country

Postcode

Thanks.

Re: How to split address?

Tue, 01 Apr 2014 15:40:20 GMT

Please see attached.

Re: How to split address?

giakoum — Tue, 01 Apr 2014 15:43:57 GMT

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.

Re: How to split address?

Tue, 01 Apr 2014 15:46:04 GMT

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

Re: How to split address?

ThornOfCrowns — Tue, 01 Apr 2014 15:47:39 GMT

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!

Re: How to split address?

giakoum — Tue, 01 Apr 2014 15:47:52 GMT

no, it will not.

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

Re: How to split address?

its_anandrjs — Tue, 01 Apr 2014 15:50:21 GMT

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

Re: How to split address?

its_anandrjs — Tue, 01 Apr 2014 15:52:59 GMT

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

=SubField(Add,1)

=SubField(Add,2)

Hope this helps

Re: How to split address?

Tue, 01 Apr 2014 15:53:37 GMT

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;

Re: How to split address?

Tue, 01 Apr 2014 15:59:28 GMT

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

Re: How to split address?

Tue, 01 Apr 2014 16:03:21 GMT

Thanks.How can I change the above expression if the data is in excel?

Re: How to split address?

giakoum — Tue, 01 Apr 2014 16:04:08 GMT

Why, why is wrong with ckipling solution? See above post

Re: How to split address?

its_anandrjs — Tue, 01 Apr 2014 16:06:58 GMT

You have to load like below

Load

Address,

SubField(Address,',',1) as ADD1,

SubField(Address,',',2) sa ADD2,

SubField(Address,',',3) as ADD3,

SubField(Address,',',4) as ADD4,

SubField(Address,',',5) as ADD5,

SubField(Address,',',6) as ADD6

From Location;

Hope this helps

Re: How to split address?

Tue, 01 Apr 2014 16:07:12 GMT

Mostly the same - use the "Table files" button to generate a load statement for that workbook and then modify it to look like below:

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;

FROM

FilePath\FileName.xlsx

(ooxml, embedded labels, table is TableName);

Re: How to split address?

giakoum — Tue, 01 Apr 2014 16:07:19 GMT

You need to decide where your data is . We started with a text file, moved on to Oracle and now it is in Excel. Are you experimenting? Logic is the same no matter the source.

Re: How to split address?

Tue, 01 Apr 2014 16:10:06 GMT

Sorry if I am not clear. I am not experimenting. I need to show the data both from SQL and excel. That's the reason I send one example as sample attached.

Re: Re: How to split address?

its_anandrjs — Tue, 01 Apr 2014 16:10:07 GMT

Check the load script for excel file

LOAD

Add,

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

FROM

(ooxml, embedded labels, table is Sheet1);

EXIT Script

change in script according to your system

Hope this helps

Re: How to split address?

Tue, 01 Apr 2014 16:11:37 GMT

Thanks. I tried this. But it seems some fields data is not showing in correct place. Is it possible to solve this?

Re: How to split address?

its_anandrjs — Tue, 01 Apr 2014 16:17:25 GMT

I believe check your data then use subfield function it will give correct address.

Hope this helps

Re: How to split address?

fkeuroglian — Wed, 02 Apr 2014 02:16:04 GMT

Use subfield , this would generate the correct fileds

what is the problem that you have

Good luck

Fernando