topic Re: How to split column values in QlikView

How to split column values

Wed, 09 Oct 2013 08:35:32 GMT

Hi,

Could anyone help me how to change the script to get correct values in fields. I have one column HomeAddress in straight table and I need to split like below

HomeAddress,

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

subfield(HomeAddress, ' , ', 2) as Address2,

subfield(HomeAddress, ' , ', 3) as Address3,

subfield(HomeAddress, ', ', 4) as Town,

subfield(HomeAddress, ', ', 5) as County,

subfield(HomeAddress, ', ', 6) as Postcode

After writing the above script I am getting values incorrect. please have a look on attched and help me please.

Thanks.

Re: How to split column values

tresB — Wed, 09 Oct 2013 08:44:30 GMT

Be careful of the spaces within the single quotes.

subfield(HomeAddress, ' , ', 1) as Address1, // extra spaces are there

subfield(HomeAddress, ',' , 1) as Address1, // remove those and try like this

Re: How to split column values

Wed, 09 Oct 2013 09:06:29 GMT

Thanks. But still it's showing the same way eventhough I remove extra spaces. Please help me.

Re: How to split column values

tresB — Wed, 09 Oct 2013 09:11:00 GMT

Best would have been, if you could share sample app with sample of data. But if that is not possible, try to extract sample of your data into excel and share here.

Re: How to split column values

Wed, 09 Oct 2013 09:26:21 GMT

Thanks and so kind of you. please have a look on attached.

Re: How to split column values

tresB — Wed, 09 Oct 2013 09:29:37 GMT

Please share HomeAddress data from which you are trying to generate different fields.

Re: How to split column values

Anonymous — Wed, 09 Oct 2013 09:30:10 GMT

I think the HomeAddress field is not well formated,the comma is not in the right position. If you put:

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

subfield(HomeAddress, ',', 2) as Address2

subfield(HomeAddress, ',', 3) as Address3

And HomeAddress field looks like this you will get the correct values

17 Long Road,25 Main Street,3 Queen street

But if you have HomeAddress field like this you wont get the correct values

17 Long Road , 25 Main Street , 3 Queen street

Or simple the HomeAddress field does not have that many values.

Hope you understand and it helps

Re: How to split column values

Wed, 09 Oct 2013 09:35:42 GMT

Hi,

The code you have written seems correct.

i am attaching a sample doccument for the same.

Re: How to split column values

Wed, 09 Oct 2013 09:37:42 GMT

Hi,

I have attached the complete script file of all tables. Please could you modify the script. As I am not getting the values in the Pivot/Straight tables when I used fields. I am not understanding where I am doing mistake. If you don't mind please could modify this so that would be really reaaly helpful as all the users are depending on this qlikview file.

It's showing blank values for some of the fields.

Please Tresesco I am requesting please help.

Thanks.

Re: How to split column values

polisetti — Wed, 09 Oct 2013 09:46:18 GMT

Just Try Like this by changing the field name

Or Please Attach your Main data sample sheet ,where you have HOMEADDRESS Field

EX:

Location,

SubField(Location,'\',1) as Region,

SubField(Location,'\',2) as Country,

SubField(Location,'\',3) as State,

SubField(Location,'\',4) as City,

SubField(Location,'\',5) as Address

" \" is the Separator in my data

Re: How to split column values

tresB — Wed, 09 Oct 2013 09:52:03 GMT

It is more about the data format in the HomeAddress_20026 field. Please check that or load that field directly into qv and show as screen shot so that we can have a look and understand.

Re: How to split column values

Wed, 09 Oct 2013 10:11:20 GMT

Thanks and please find attached and please could you help me in modifying the script as the straight/pivot tables are showing blank values eventhough there is data in SQL table.

Re: How to split column values

Anonymous — Wed, 09 Oct 2013 13:09:40 GMT

Amelia please show us the values in the field Home_address_20026 so we can help you