Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

dmv15956
Contributor

Find Match Value

Hi All,

I am trying to find match value from "Match Name" list in "Full Name" List. If its match then I need output Match Name.

I use index and match formula in excel, but I am unable to figure it out in Qlikview.

5 Replies
MVP
MVP

Re: Find Match Value

May be this

MappingTable:

Mapping

LOAD [Match Name],

  '@' & [Match Name] & '%'

FROM

[..\..\Downloads\Match.xlsx]

(ooxml, embedded labels, table is [Match Name]);

Table:

LOAD [Full Name],

    TextBetween(MapSubString('MappingTable', [Full Name]), '@', '%') as [Need Output Here]

FROM

[..\..\Downloads\Match.xlsx]

(ooxml, embedded labels, table is [Full Name]);

dmv15956
Contributor

Re: Find Match Value

Thanks Sunny,

This works, If possible can you explain me how this works. Why @ & %, Can't I apply direct Mapsubstring

MVP
MVP

Re: Find Match Value

Without Text Between, you will just replace the specific text with a new value (or same value in your case) Test the below out.

MappingTable:

Mapping

LOAD [Match Name],

  [Match Name]

FROM

[..\..\Downloads\Match.xlsx]

(ooxml, embedded labels, table is [Match Name]);

Table:

LOAD [Full Name],

     MapSubString('MappingTable', [Full Name]) as [Need Output Here]

FROM

[..\..\Downloads\Match.xlsx]

(ooxml, embedded labels, table is [Full Name]);

The idea is to add a unique character in the front and end of the text you want to see and then use Text between function to pull the specific text out of the whole thing

dmv15956
Contributor

Re: Find Match Value

Thank Sunny,

Very helpful. I have one more question.

How to find match value from "Match Name" list in "Full Name" List. If its match then I need output Match Code value but not the match name this time.

I have attached the file for example.

MVP
MVP

Re: Find Match Value

Try this

MappingTable:

Mapping

LOAD [Match Name],

  '@' & Code & '%'

FROM

[..\..\Downloads\Match (1).xlsx]

(ooxml, embedded labels, table is [Match Name]);

Table:

LOAD [Full Name],

    TextBetween(MapSubString('MappingTable', [Full Name]), '@', '%') as [Need Output Here]

FROM

[..\..\Downloads\Match.xlsx]

(ooxml, embedded labels, table is [Full Name]);

Capture.PNG