Lookup() values that are not identical

[martyn_birzys]

Hi All,

I have two two tables, I need to be able to link them on fields Account and Narrative.

I could use lookup() if values were identical, but they're not.

I could use match(), if I had small number of variables but there are 400+ variables in the Account table, and several thousand in the Narrative.

Any ideas?

Account	Narrative
ABCD	PI / ABCD
DEF 1	PI / DEF 1textextext
GHI	PI / GHI / ref
JKL	PI / JKL

[sunny_talwar]

Not sure what exactly you are trying to do here. Would you be able to elaborate a little more please?

[maxgro]

maybe with wildmatch, an example could be

load *, WildMatch(Narrative, '*' & Account & '*') as Match inline [

Account, Narrative

ABCD, PI / ABCD

DEF 1, PI / DEF 1textextext

GHI, PI / GHI / ref

JKL, PI / JKL

AAA, BAABAHHH

];

[martyn_birzys]

Apologies for not being clear. I have two tables that I need to link.

Account	Description
ABCD	ABC Company
DEF 1	DEF Company
GHI	GHI Company
JKL	JKL Company
Narrative	Transaction Value
PI / ABCD	100
PI / DEF 1textextext	200
PI / GHI / ref	300
PI / JKL	400

The key fields are Account and Narrative. The Narrative field always contains the string same as in Account field, but there is no rule how to extract it like using Subfield() because of spaces and other characters.

If I only had a handful of records, I could do match(Narrative, '*ABCD*', '*GHI*', etc), but I have 400+ records.

I hope I made myself clearer.

[sunny_talwar]

One option, although not the most efficient could be this:

Table:

LOAD * Inline [

Account, Description

ABCD, ABC Company

DEF 1, DEF Company

GHI, GHI Company

JKL, JKL Company

];

Join(Table)

LOAD * Inline [

Narrative, Transaction Value

PI / ABCD, 100

PI / DEF 1textextext, 200

PI / GHI / ref, 300

PI / JKL, 400

];

Temp:

NoConcatenate

LOAD *,

  If(SubStringCount(Narrative, Account) > 0, 1, 0) as Flag

Resident Table;

FinalTable:

NoConcatenate

LOAD *

Resident Temp

Where Flag = 1;

DROP Tables Temp, Table;

Output:

[maxgro]

see attachment

[sunny_talwar]

This one is a little more efficient then the previous one, but still might not be the most optimized one:

Table:

LOAD * Inline [

Account, Description

ABCD, ABC Company

DEF 1, DEF Company

GHI, GHI Company

JKL, JKL Company

];

Join(Table)

LOAD * Inline [

Narrative, Transaction Value

PI / ABCD, 100

PI / DEF 1textextext, 200

PI / GHI / ref, 300

PI / JKL, 400

];

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where SubStringCount(Narrative, Account) > 0;

DROP Table Table;

[sunny_talwar]

maxgro‌ can you share your script please? I would love to see how you have done this?

Thanks,

Sunny

[sunny_talwar]

[maxgro]

t1:

load rowno() as AId, Account inline [

Account, Narrative

ABCD

DEF 1

GHI

JKL

AAA

];

t2:

load rowno() as NId, Narrative inline [

Narrative

PI / ABCD

PI / DEF 1textextext

PI / GHI / ref

PI / JKL

BAABAHHH

ZZZZZZZZZZ

ZAAAZ

YAAAY

];

t3:

NoConcatenate load  AId, Account as Account2 Resident t1;

join (t3) load NId, Narrative as Narrative2 resident t2;

t4:

load * Where Match;

load *,  WildMatch(Narrative2, '*' & Account2 & '*') as Match

Resident t3;

DROP Table t3;