topic Re: Link non matching data in QlikView

Link non matching data

Fri, 01 Nov 2013 16:42:55 GMT

Hi everyone,

I am struggling to get two tables to connect properly. In both tables I have the connecting key "Trading postcode" but the problem is that in one of my data sets I am using the full post code etc. W3 BT6, whereas in my other table I am only using the outcode. In this example it would then be W3.

I would like QV to associate the data only using the outcode. So that everything up until a space would be compared and then associated. in this case everything from the first data set starting with W3 should be matched to the W3 in the other set.

I have tried using a wildcard in different places but nothing seems to work.

Thanks in advance,

Andreas Parker

Re: Link non matching data

Clever_Anjos — Fri, 01 Nov 2013 16:48:44 GMT

Create another field at script using

subfield([Trading postcode],' ',1) as mykey

Re: Link non matching data

israrkhan — Fri, 01 Nov 2013 16:56:38 GMT

Load an other field,

load Id,

post code,

Left (post code,2) as Key.

from abc...

Re: Link non matching data

Fri, 01 Nov 2013 17:03:55 GMT

Thanks for the quick reply!

So i should load this exact statement into both my tables. like:

Table1:

load

[Trading postcode],

[Company Nane],

subfield([Trading postcode],' ',1) as mykey'

From

Example.csv

Table2:

load

[Trading postcode],

[Distance in miles],

subfield([Trading postcode],' ',1) as mykey;

From

Example2.csv.

Qlikview says that the subfield is an unknown statement. Am i missing something?

Re: Link non matching data

Clever_Anjos — Fri, 01 Nov 2013 17:06:21 GMT

There´s a quote after first mykey, please check it

Re: Link non matching data

Clever_Anjos — Fri, 01 Nov 2013 17:07:30 GMT

Should be:

Table1:

load

[Trading postcode],

[Company Nane],

subfield([Trading postcode],' ',1) as mykey

From

Example.csv;

Table2:

load

[Trading postcode],

[Distance in miles],

subfield([Trading postcode],' ',1) as mykey

From

Example2.csv;

Re: Link non matching data

Fri, 01 Nov 2013 17:19:20 GMT

The connection works perfectly now, problem is that the "Trading postcode" labels are still

associating as well.

Is it completely wrong to do it like this:

Table1:

load

[Trading postcode],

[Company Nane],

subfield([Trading postcode],' ',1) as mykey;

From

Example.csv

Table2:

load

[Trading postcode] as Outcodes,

[Distance in miles],

subfield(Outcodes,' ',1) as mykey;

From

Example2.csv.

Or would it still be considered [Trading postcode] in the scribt?

Re: Link non matching data

Fri, 01 Nov 2013 17:22:21 GMT

and my mistakes with the ";" and ' being miss placed. They are written as you show in the scribt

Re: Link non matching data

Clever_Anjos — Fri, 01 Nov 2013 17:24:28 GMT

You want "I would like QV to associate the data only using the outcode. " right?

So you could skip or rename (you´ve done already) [Trading postcode] from one table

Re: Link non matching data

Fri, 01 Nov 2013 17:27:22 GMT

Yes, this would solve your problem.

Re: Link non matching data

Fri, 01 Nov 2013 17:43:14 GMT

Now everything works as it should in regards to connecting the tables.

Only problem is that it does not associate the postcode with the Outcodes.

when i write:

subfield([Trading postcode],' ',1) as mykey

the "1" should be the field number. Is the the number of the fields loaded in the ongoing statement? for example

Load

From

Example;

then z would be field no. 3 right?

Re: Link non matching data

Fri, 01 Nov 2013 18:02:53 GMT

replace

subfield(Outcodes,' ',1) as mykey;

with

Outcodes as mykey

Re: Link non matching data

Fri, 01 Nov 2013 18:05:47 GMT

It still does not seem like the postcodes and outcodes are matched.

subfield(Outcodes,' ',1) as mykey;

Am i right when I say that there is a space between the two ' '. This should be implemented into qlikview as well right?

Thanks a lot!. This works 99% and I am sure that I can get it to work shortly.

Re: Link non matching data

Fri, 01 Nov 2013 18:16:02 GMT

It works! I had to manually add the space and everything was good.

Clever Anjos I need to give you a beer at some point. You saved my day as well as my inner peace throughout the weekend.

Ok to be serious. Thanks a lot, this was a relief.

Kind regards

Andreas Parker