How to get correct values

[#1]

Hi,

I have a straight table with fields as below

Address1= =SUBFIELD(Home_Address_20026,',',1)

Address2=  =If(Address2<>Postcode,SUBFIELD(Home_Address_20026,',',2))

Address3=  =If(Address3<>Postcode,SUBFIELD(Home_Address_20026,',',3))

Town=  =If(Town<>Postcode,SUBFIELD(Home_Address_20026,',',4))

County= =If(County<>Postcode,SUBFIELD(Home_Address_20026,',',5))

PostCode=  =Subfield(Home_Address_20026,',',-1)

Here Home_Address_20026 is the column from sql table.

When I am using above in expressions it is showing values incorrect order. I,e under address3 it is showing Town values and under County it is showing Adrress2 values and so on.

Please could anyone help me how to set this to get correct order values.

Thanks.

[nilesh_gangurde]

U have licence version of Qv ??

also attaching again.

-Nilesh

[#3]

Thanks and it is showing values correct only under Postcode and remaining columns all are showing incorrect order as earlier.

Please help how to set this.

[magavi_framsteg]

Amelia,

It is very hard, close to impossible, to divide free text columns into logical parts.

Especially when the delimiter occurs naturally in a logical field, for example address.

For the above method to work, you need a delimiter that does not occur in the logical field itself, for example a semicolon, hash sign, tilde etc.

Spaces, commas, dots and such common chars can occur in both names, addresses, cities and such.

Kind regards

Magnus Åvitsland

BI Architect Consultant

Framsteg Business Intelligence Corp.

[#5]

Thanks and in that case how can I sort this with out changing anything in source sql table. will qlikview able to do this in recent version?

[magavi_framsteg]

One good thing would be to separate the input of county, city, addresses and postcode into separate columns in the data entry system. Otherwise you would never be able to group for example sum of sales per city or county.

To have them concatenated like this in free text format is maybe useful for show only. Like having a free text comment showing beside each order or something.

However ff you would like to group data on it and make decisions from it, you really need to separate the fields in some ways, else you can never trust what is in your charts/tables.

Go ask the DB/DW guys if they can create a view for you where they have the columns separated.

Maybe more normalized data exist in some other table?

[magavi_framsteg]

And not only separation the data entry into several fields, also make sure the County and City are selected from pre defined lists, not free text entry.

Else you will have problems grouping the data.

Consider this:

sum sales per city

LA $200

L.A $5500

LosAngeles $3939

Los Angeles $39383737

Los angeles $49494

Lost angels $3939

los angeles $233

losangeles $22

la $234

l.a $99

Do you see where I'm going?

[#8]

Thanks for the reply.

[#9]

Thanks and a little bit confused here could you explain a bit more for me please.