topic Re: Lookup() values that are not identical in QlikView

Lookup() values that are not identical

martyn_birzys — Wed, 07 Oct 2015 16:38:30 GMT

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

Re: Lookup() values that are not identical

sunny_talwar — Wed, 07 Oct 2015 16:56:38 GMT

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

Re: Lookup() values that are not identical

maxgro — Wed, 07 Oct 2015 16:56:44 GMT

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

];

Re: Lookup() values that are not identical

martyn_birzys — Wed, 07 Oct 2015 17:35:29 GMT

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.

Re: Lookup() values that are not identical

sunny_talwar — Wed, 07 Oct 2015 17:44:26 GMT

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:

Re: Lookup() values that are not identical

maxgro — Wed, 07 Oct 2015 17:50:20 GMT

see attachment

Re: Lookup() values that are not identical

sunny_talwar — Wed, 07 Oct 2015 17:52:10 GMT

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;

Re: Lookup() values that are not identical

sunny_talwar — Wed, 07 Oct 2015 17:52:57 GMT

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

Thanks,

Sunny

Re: Lookup() values that are not identical

sunny_talwar — Wed, 07 Oct 2015 17:53:44 GMT

Re: Lookup() values that are not identical

maxgro — Wed, 07 Oct 2015 17:55:39 GMT

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;