topic Re: Can I do contains function when join two tables in QlikView

Can I do contains function when join two tables

Thu, 26 May 2016 18:36:56 GMT

Hello Everyone,

I have two table. I want to join this two table when Key1 contains Key2.

Table 1
AAAAA
BBBBB
ABABC
BABAB
CABC
DECFG

Table 2
AAA
BB
ABA
DEF
FDC
GHJ

Result	Result
AAAAA	AAA
BBBBB	BB
ABABC	ABA
BABAB
CABC
DECFG

Thank you very much in advance!

Re: Can I do contains function when join two tables

sunny_talwar — Thu, 26 May 2016 18:39:23 GMT

The fourth row BABAB also contains ABA, what is the reason to not have ABA for BABAB?

Re: Can I do contains function when join two tables

Kushal_Chawda — Thu, 26 May 2016 18:43:52 GMT

I think he needs match string from start

Re: Can I do contains function when join two tables

Thu, 26 May 2016 18:45:07 GMT

Sunny - Thank you for your question.

Consider each letter is one level from left to right. We can drill up and drill down on this sequence.

Re: Can I do contains function when join two tables

sunny_talwar — Thu, 26 May 2016 18:46:18 GMT

dust0000 wrote:

Consider each letter is one level from left to right. We can drill up and drill down on this sequence.

I don't think I understand, kushal‌‌ do you?

Re: Can I do contains function when join two tables

Thu, 26 May 2016 19:43:33 GMT

Hi Sunny,

Please see attached excel file for sample data. I changed this sample data.

I want Table1.ID contains Table2.ID.

Thank you very much!

Re: Can I do contains function when join two tables

maxgro — Thu, 26 May 2016 19:59:57 GMT

Maybe

RESULT

SCRIPT

Table1:

LOAD * Inline [

AAAAA

BBBBB

ABABC

BABAB

CABC

DECFG

];

Join (Table1)

LOAD * Inline [

AAA

ABA

DEF

FDC

GHJ

];

Final:

LOAD *, F1 as F1Check Where FlagContains;

LOAD

F1,

F2,

wildmatch(F1, F2 & '*') as FlagContains

Resident Table1;

Concatenate (Final)

LOAD Distinct

Resident Table1

Where not Exists(F1Check, F1);

DROP Table Table1;

Re: Can I do contains function when join two tables

sunny_talwar — Thu, 26 May 2016 20:23:33 GMT

Another way similar to maxgro‌'s solution:

MappingTable:

Mapping

LOAD F2,

'/' & F2 & '\' as F22

Inline [

AAA

ABA

DEF

FDC

GHJ

];

Table1:

LOAD *,

If(WildMatch(F1, TextBetween(MapSubString('MappingTable', F1), '/', '\') & '*') and Len(Trim(MapSubString('MappingTable', F1))),

TextBetween(MapSubString('MappingTable', F1), '/', '\')) as F2;

LOAD * Inline [

AAAAA

BBBBB

ABABC

BABAB

CABC

DECFG

];

Re: Can I do contains function when join two tables

Thu, 26 May 2016 22:19:43 GMT

maxgro and Sunny,

You guys are great. This is just want I want.

You guys have great long weekend!

Best regards!