topic Re: How to get one column value split in to multiple columns in QlikView

How to get one column value split in to multiple columns

Mon, 13 Jan 2014 09:58:20 GMT

Hi,

Could anyone help me how can I split the Home values in to

Address1, Address2, Address3, Town ,County, Postcode.

Please find attached. I have been using SUBFIELD

=SUBFIELD(Home,',',1)

=If(Address2<>Postcode,SUBFIELD(Home,',',-1))

=If(Address3<>Postcode,SUBFIELD(Home,',',-2))

=If(Town<>Postcode,SUBFIELD(Home,',',-3))

=If(County<>Postcode,SUBFIELD(Home,',',-4))

=SUBFIELD(Home,',',-1)

but it showing incorrect values
Please any solution to sort this problem.

Thanks.

Re: How to get one column value split in to multiple columns

Mon, 13 Jan 2014 10:10:58 GMT

Hi Amelia,

You can use Subfield function to get the desired result. Can you explain, in:

138 Elmsdale Avenue, Coventry, CV6 6EU

What is it that you want as Address1, Address2 etc.

Re: How to get one column value split in to multiple columns

Mon, 13 Jan 2014 10:15:11 GMT

Hi,

Thanks and I need

Address1 = 138 Elmsdale Avenue

Address2=

Address3=

Town=

County= Coventry

Postcode= CV6 6EU

Re: How to get one column value split in to multiple columns

Mon, 13 Jan 2014 10:23:58 GMT

Hi Amelia,

Please find the attached file for the solution.

Santhosh G

Re: How to get one column value split in to multiple columns

Mon, 13 Jan 2014 10:28:56 GMT

Hi Amelia,

I can see that there is an inconsistency in the delimiter position, in order to be able to use subfield function, you should have delimiters at fixed positions.

Re: How to get one column value split in to multiple columns

christian77 — Mon, 13 Jan 2014 10:32:44 GMT

Have I seen this before?

The same question buy the same person.

The answer is the same.

Free texts are difficult to treat and analize.

Re: How to get one column value split in to multiple columns

Mon, 13 Jan 2014 10:36:02 GMT

Is there any solution to fix this because that's how I have data in SQL table. Any solution please with out changing the delimiter position.

Re: How to get one column value split in to multiple columns

Anonymous — Mon, 13 Jan 2014 13:19:02 GMT

Hi Amelia,

i written below script for Address Calculation,but problem is some CamCount=4 then City is coming in 3rd position,other please it's coming in 4th Place,below script is failure those conditions,CamCount=8,i didn't under stand.how i want to take.

Flat 68, Arundel Court, London, 0 Lansdowne Road, N17 0LR

Directory;

load *,

if(CamCount=2,SubField(Home,',',1),

if(CamCount=3,SubField(Home,',',1),

if(CamCount=4,SubField(Home,',',1)))) AS Address1,

if(CamCount=3,SubField(Home,',',2),

if(CamCount=4,SubField(Home,',',2))) AS Address2,

if(CamCount=4,SubField(Home,',',3)) AS Address3,

if(CamCount=2,SubField(Home,',',2),

if(CamCount=3,SubField(Home,',',3),

if(CamCount=4,SubField(Home,',',4)))) AS City,

if(CamCount=2,SubField(Home,',',3),

if(CamCount=3,SubField(Home,',',4),

if(CamCount=4,SubField(Home,',',5)))) AS PinCode;

LOAD

Home,

SubStringCount(Home,',') AS CamCount

// SubField(Home,',',1) As Address1,

// SubField(Home,',',2) As Address2,

// SubField(Home,',',3) As Address3,

// SubField(Home,',',4) As Address4

FROM

Address_Person.xlsx

(ooxml, embedded labels, table is Sheet1);

Thanks

SHAIK

Re: How to get one column value split in to multiple columns

Mon, 13 Jan 2014 13:45:10 GMT

Thanks and how can I select columns in chart item please help me