How to split column values

[#1]

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.

[tresesco]

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

[#3]

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

[tresesco]

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.

[#5]

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

[tresesco]

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

[Anonymous]

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

[#8]

Hi,

The code you have written seems correct.

i am attaching a sample doccument for the same.

[#9]

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.

[polisetti]

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