topic Re: Keyword Searching between 2 Different Tables in QlikView

Keyword Searching between 2 Different Tables

Pulkit_Thukral — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

i am creating a logic for keyword searching for the below scenario:

i have 2 different excels :

1st excel has Keyword

2nd excel has 'id' written in the description field so we have to do wildmatch for searching.

Example:

1st Excel :

Keyword

ABC

DEF

XYZ

2nd Excel:

Ticket State -------- Description

1 new -------- ORA:ABC-daskjasdas

2 closed -------- SRV:DEF-sadra

My Output should be joining both the tables based on 1st excel field(left join)

Output:

Keyword Ticket State -------- Description

ABC 1 new -------- ORA:ABC-daskjasdas

DEF 2 closed -------- SRV:DEF-sadra

as i found keyword written in description field for these 2 keywords only.

Re: Keyword Searching between 2 Different Tables

tamilarasu — Fri, 27 May 2016 04:54:59 GMT

Hi Pulikit,

Try this script.

Temp:
LOAD Keyword
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);

Join

LOAD [Ticket     ],
     [State     ],
     [--------     ],
     Description
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet1);

Noconcatenate
Result:
LOAD *
Resident Temp
where WildMatch(Description, '*' & Keyword & '*');

DROP Table Temp;

Re: Keyword Searching between 2 Different Tables

MindaugasBacius — Fri, 27 May 2016 04:58:32 GMT

Description field contains those values, right?

ORA:ABC-daskjasdas

SRV:DEF-sadra

Well I offer you trying few ways for finding the Key:

=mid(trim(Description), 5, 3)
=subfield(replace(trim(Description), '-', ':'), ':', 2)

Re: Keyword Searching between 2 Different Tables

tresB — Fri, 27 May 2016 05:02:54 GMT

If you have small data set, you can go with cross join and then filter using where clause as shown by Tamil above. If you have big volume data, you should avoid the cross join and try like:

Map:

Mapping Load

Keyword as Keyword1,

'<'&Keyword&'>' as Keyword

Inline [

Keyword

ABC

DEF

GHI

];

Table1:

Load *

Inline [

Keyword

ABC

DEF

GHI

];

Inner join

Table2:

load

Ticket,

State,

Description,

TextBetween(Mapped,'<','>') as Keyword

Where Mapped<>Description;

Load

MapSubString('Map',Description) as Mapped;

mapping Load * Inline [

Ticket, State, Description

1, new, ORA:ABC-daskjasdas

2, closed, SRV:DEF-sadra

];

Re: Keyword Searching between 2 Different Tables

Kushal_Chawda — Fri, 27 May 2016 05:38:35 GMT

T1:

LOAD

Upper(trim(Keyword)) as Keyword

FROM table;

left join(T1)

LOAD

Ticket,

State,

Description,

Upper(trim(Textbetween(Description,':','-'))) as Keyword

FROM T2;

Re: Keyword Searching between 2 Different Tables

Pulkit_Thukral — Fri, 27 May 2016 05:42:32 GMT

Thanks Tamil.It worked perfectly

Re: Keyword Searching between 2 Different Tables

Kushal_Chawda — Fri, 27 May 2016 05:45:55 GMT

Make sure that you are performing cross join here.. which may cause performance issue if data set increased.

Re: Keyword Searching between 2 Different Tables

qlikview979 — Fri, 27 May 2016 06:02:01 GMT

Hi Nagaraj,

How your Wild match Condition will work ? Please Explain ?

Regards,

Mahesh.

Re: Keyword Searching between 2 Different Tables

tamilarasu — Fri, 27 May 2016 07:57:26 GMT

Hi Mahesh,

If we join the two tables, the result looks like below.

In the resident load, we can use wildmatch to load only the matched rows.

Re: Keyword Searching between 2 Different Tables

qlikview979 — Fri, 27 May 2016 08:03:37 GMT

Hi Nag,

I got It . in your Expression

where WildMatch(Description, '*'& Keyword &'*' );

after Keyword How it will work "*"

Regards,

Mahesh.

Re: Keyword Searching between 2 Different Tables

sunny_talwar — Fri, 27 May 2016 09:23:25 GMT

I think this is how I would do it as well. No need to do a heavy cross join as kushal‌ has already pointed out.

Re: Keyword Searching between 2 Different Tables

qlikview979 — Fri, 27 May 2016 10:45:10 GMT

Hi Nag,

I got It . in your Expression

where WildMatch(Description, '*'& Keyword &'*' );

after Keyword How it will work "*"

Regards,

Mahesh.

Re: Keyword Searching between 2 Different Tables

Kushal_Chawda — Fri, 27 May 2016 10:53:59 GMT

Yeah.. should go with tresesco‌ suggestion.