Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]);
Thanks Sunny,
This works, If possible can you explain me how this works. Why @ & %, Can't I apply direct Mapsubstring
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
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.
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]);