Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DipeshVadgama
Partner - Creator II
Partner - Creator II

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
sunny_talwar

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]);

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

Thanks Sunny,

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

sunny_talwar

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

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

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.

sunny_talwar

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