topic Re: Wildcard search need help urgent in QlikView

Wildcard search need help urgent

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a huge list of entries in excel for example

MatchKey Category

*qwerty* critical

*asdfg* medium

*organic* high

*metal* low

*sql* medium

I need to match the matchkey with the available list of hostnames.

for example if my hostname is

Hostname

qwertykeyboard

asdfgcfghkodddgdfgfg

metalicboard

sqlquery

organicapple

my output should be like if i select any hostname the category should be reflected

if I select qwertykeyboard the Cateogry critical should be reflecting.

Thanks in advance

Re: Wildcard search need help urgent

trdandamudi — Sat, 07 May 2016 18:29:32 GMT

May be like below:

Map_Table:

Mapping Load * Inline [

Matchkey, Category

*qwerty*, critical

*asdfgc*, medium

*organi*, high

*metali*, low

*sqlque*, medium

];

Host_Table:

Load * Inline [

Hostname

qwertykeyboard

asdfgcfghkodddgdfgfg

metalicboard

sqlquery

organicapple

];

NoConcatenate

Final_Table:

Load

Hostname,

ApplyMap('Map_Table','*'&Mid(Hostname,1,6)&'*') as Category

Resident Host_Table;

Drop table Host_Table;

Note: Please note that I restricted the MatchKey field to length of 6 characters to get this working..

Hope this helps...

Re: Wildcard search need help urgent

swuehl — Sat, 07 May 2016 20:42:32 GMT

I assume you also want to find matches in the mid or end of a Hostname, and I also assume that you only want to map a single category, i.e. there are no multiple categories to match (which is possible with a different script).

MAP:
Mapping Load 
PurgeChar(Matchkey,'*') as Matchkey, '\/'&Category&'/\' as Category Inline [
Matchkey,       Category
*qwerty, critical
*asdfgc*,  medium
*organi*, high
*metali*,   low
*sqlque*,      medium
]; 



Host_Table:
Load * Inline [
Hostname
qwertykeyboard
asdfgcfghkodddgdfgfg
metalicboard
sqlquery
organicapple
];


NoConcatenate
Final_Table:
Load
Hostname,
Textbetween(MapSubString('MAP',Hostname),'\/','/\') as Category
Resident Host_Table
;




Drop table Host_Table;

Re: Wildcard search need help urgent

Mon, 09 May 2016 05:50:10 GMT

This is not work Thirumala, I want to show for the selected hostname which is the matching category.

If I select metaliboard, category low should be displayed

Re: Wildcard search need help urgent

Mon, 09 May 2016 05:50:55 GMT

This is not working.

For selected hostname I need respective category.

Re: Wildcard search need help urgent

Kushal_Chawda — Mon, 09 May 2016 05:57:27 GMT

One more way

Data:

LOAD HostName,

pick( wildmatch(lower(HostName),'*qwerty*','*asdfg*','*organic*','*metal*','*sql*'),

'critical','medium','high','low','medium') as Category

FROM Table

Re: Wildcard search need help urgent

Mon, 09 May 2016 05:59:47 GMT

Thanks for the solution, But I have wildcard search around 3000 to match with Hostname.

Will this work then?

Re: Wildcard search need help urgent

trdandamudi — Mon, 09 May 2016 16:29:53 GMT

If you create a list box and select a hostname it will show you the category. I created a list box and when I selected Metalicboard it is displaying 'Low' as the category. Please see the attached file:

Re: Wildcard search need help urgent

Mon, 09 May 2016 17:46:38 GMT

Hi Thirumala,

You have fixed the length to 6 in Mid() function but my match list have variable length, which is not giving me the accurate result.

Re: Wildcard search need help urgent

Kushal_Chawda — Mon, 09 May 2016 17:58:53 GMT

// list out 3000 Match key

Data:

LOAD *, RowNo() as Row inline [

MatchKey, Category

qwerty, critical

asdfg, medium

organic, high

metal, low

sql, medium ];

New:

lOAD

concat( chr(39) &'*'& lower(MatchKey) &'*'& chr(39),',',Row ) as MatchKey,

concat( chr(39) & lower(Category) & chr(39),',',Row ) as Category

Resident Data;

DROP Table Data;

let vMatchKey = peek('MatchKey',0,'New');

let vCategory = peek('Category',0,'New');

Data:

LOAD HostName,

pick(wildmatch(lower(HostName),$(vMatchKey)),$(vCategory)) as Category

FROM Table;

drop table New;

Re: Wildcard search need help urgent

Mon, 09 May 2016 19:30:26 GMT

Hi thirurumala

Please find the sample sheet having hostname nd repective maptable fields to give the accurate result.

Thanks in advance

Re: Wildcard search need help urgent

swuehl — Mon, 09 May 2016 19:52:50 GMT

Mehare Nigar wrote:

This is not working.

For selected hostname I need respective category.

This is not working is not a good issue description.

Have you even had a look at the sample I've attached?

What's wrong with the Categories that have been mapped to Hostname?