topic Re: Link two frields by Like/Match in expression or script in QlikView

Link two frields by Like/Match in expression or script

darren_dixon — Tue, 13 Nov 2012 10:12:40 GMT

Hi,

I need to match/like two fields, Order Number and Job ID by the Job ID.

This can be in either an expression or in the script. I would like to use this in a straight table.

The Order Number field has GEN- at the front.

The Job ID is 3 letters and 7 numbers

The Order Number has 2 numbers and one letter at the end.

Example data:

Order Number: Job ID:

GEN-HSG2024471000P HSG2024471

GEN-HSG2024472000C HSG2024472

GEN-HSG2024473000G HSG2024473

GEN-HSG2024474000B HSG2024474

I had tried the following but did not work.

if([Order Number] LIKE [Job ID], [Order Number], "NULL")

if([Order Number] LIKE *[Job ID]*, [Order Number], "NULL")

Thanks for your help.

Thanks,

Darren

Re: Link two frields by Like/Match in expression or script

swuehl — Tue, 13 Nov 2012 10:15:03 GMT

This might work:

if([Order Number] LIKE '*'& [Job ID] & '*', [Order Number], 'NULL')

Re: Link two frields by Like/Match in expression or script

darren_dixon — Tue, 13 Nov 2012 10:53:35 GMT

Thank you for your quick response.

Works great!

Re: Link two frields by Like/Match in expression or script

darren_dixon — Tue, 13 Nov 2012 11:02:44 GMT

I've tested this in a straight table and works great.

The Order Number is in one table and Job ID is in another.

How would I join these two in a script to create a relationship?

Thanks,

Darren

Re: Link two frields by Like/Match in expression or script

swuehl — Tue, 13 Nov 2012 11:13:05 GMT

How are these two tables linked to each other (by which key field)?

You can also try

mid([Order Number],5,10) as [Job ID]

in the load of your order Table, if the Number layout is always embedded into order number like this.

Then your tables will be linked by Job ID.

Re: Link two frields by Like/Match in expression or script

darren_dixon — Tue, 13 Nov 2012 11:18:34 GMT

The two tables are below.

The join between the two tables would be the Order Number and Job ID

Repairs Costs;

LOAD [Job ID],

[V Labour Cost],

[V Material Cost],

[V Plant Cost],

[V Sub Contract Cost],

[V Total Cost]

FROM

[..\..\..\..\Responsive Repairs\Repairs_Job Costs.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq, header is 14);

Handheld:

LOAD "Contract",

OrderNumber AS "Order Number",

SQL SELECT *

FROM handheld.dbo."tblXWR_Appointments";

Re: Link two frields by Like/Match in expression or script

swuehl — Tue, 13 Nov 2012 11:21:33 GMT

So have you tried:

Handheld:

LOAD "Contract",

mid([OrderNumber],5,10) as [Job ID],

OrderNumber AS "Order Number";

SQL SELECT *

FROM handheld.dbo."tblXWR_Appointments";

which should link your two tables?

Re: Link two frields by Like/Match in expression or script

darren_dixon — Tue, 13 Nov 2012 11:28:01 GMT

That's worked great.

Thank you for your help and support.

Darren