Keyword Searching between 2 Different Tables

[Pulkit_Thukral]

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.

[tamilarasu · Accepted solution]

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;

[MindaugasBacius]

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)

[tresesco]

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

];

[Kushal_Chawda]

T1:

LOAD

Upper(trim(Keyword)) as Keyword

FROM table;

left join(T1)

LOAD

    Ticket,

    State,

    Description,

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

FROM T2;

[Pulkit_Thukral]

Thanks Tamil.It worked perfectly

[Kushal_Chawda]

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

[qlikview979]

Hi Nagaraj,

How your Wild match Condition will work ? Please  Explain ?

Regards,

Mahesh.

[tamilarasu]

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. 

[qlikview979]

Hi Nag,

I got It . in your Expression

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

after Keyword  How it will work "*"

Regards,

Mahesh.