Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Link non matching data

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

1 Solution

Accepted Solutions
Employee
Employee

Re: Link non matching data

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;

13 Replies
Employee
Employee

Re: Link non matching data

Create another field at script using

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

israrkhan
Valued Contributor II

Re: Link non matching data

Load an other field,

like

load Id,

post code,

Left (post code,2) as Key.


from abc...


Not applicable

Re: Link non matching data

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?

Employee
Employee

Re: Link non matching data

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

Employee
Employee

Re: Link non matching data

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;

Not applicable

Re: Link non matching data

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?

Not applicable

Re: Link non matching data

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

Employee
Employee

Re: Link non matching data

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

Not applicable

Re: Link non matching data

Yes, this would solve your problem.

Community Browser