topic Re: Re: How to get correct values in QlikView

How to get correct values

Tue, 17 Dec 2013 12:00:41 GMT

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.

Re: How to get correct values

nilesh_gangurde — Tue, 17 Dec 2013 12:03:01 GMT

Hi,

Please attach the sample qvw file.

-Nilesh

Re: How to get correct values

preminqlik — Tue, 17 Dec 2013 12:04:39 GMT

provide sample data fields

Re: How to get correct values

Anonymous — Tue, 17 Dec 2013 12:06:37 GMT

This could be as you are using ',' as your delimiter,

so There might be a ',' in address3 field also, due to which wrong values are reflected.

Please post some sample data , which would help me to check the issue.

Regard

Nitin

Re: How to get correct values

Tue, 17 Dec 2013 12:33:20 GMT

Please find attached.

Re: How to get correct values

Tue, 17 Dec 2013 12:34:46 GMT

Please find attached.

Re: How to get correct values

Tue, 17 Dec 2013 12:43:54 GMT

You have inconsistancy in the number of delimiters ',' in your address field B. they have to be correct, before you can use this method.

Re: How to get correct values

Tue, 17 Dec 2013 12:47:58 GMT

Thanks and there is no possibility for me to change the onconsistancy as it is the field values in SQL table which I don't have write access.

Re: Re: How to get correct values

nilesh_gangurde — Tue, 17 Dec 2013 12:48:19 GMT

Hope this will help you

-Nilesh

Re: How to get correct values

Tue, 17 Dec 2013 12:56:10 GMT

Hi,

When I am try to opening the document it is showing "Failed to Load"

I am not able to open the document please help

Re: Re: How to get correct values

nilesh_gangurde — Tue, 17 Dec 2013 13:00:42 GMT

U have licence version of Qv ??

also attaching again.

-Nilesh

Re: How to get correct values

Tue, 17 Dec 2013 13:19:56 GMT

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.

Re: Re: How to get correct values

magavi_framsteg — Tue, 17 Dec 2013 15:12:04 GMT

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.

Re: How to get correct values

Tue, 17 Dec 2013 15:32:59 GMT

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?

Re: How to get correct values

magavi_framsteg — Tue, 17 Dec 2013 15:44:16 GMT

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?

Re: How to get correct values

magavi_framsteg — Tue, 17 Dec 2013 15:51:53 GMT

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?

Re: How to get correct values

Tue, 17 Dec 2013 15:52:12 GMT

Thanks for the reply.

Re: How to get correct values

Wed, 18 Dec 2013 09:08:36 GMT

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