Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
prahlad_infy
Creator II
Creator II

How to fetch all records from another table based on wildmatch match ?

Hi All ,

How to fetch all records from another table based on wildmatch match .

Input1
Al Nino Effect
Fhad International
Petro SBM LTD
AXN Alpha Business Management
GainX
Busnos
Bina Hex Nino Trip
Gornrs Business Firm
Capital Managerment
Birech Minima Capital
Bank of Am

 

Input2
Nino
Capital Bank
ASAD Fhad Corp
SBM
Purple Business Management
GainX
Alnox Busnos Peter
Capital DAX Management

 

Output

 

Input2Search Result1Search Result2Search Result3
NinoAl Nino EffectBina Hex Nino Trip 
Capital BankCapital ManagermentBirech Minima CapitalBank of Am
ASAD Fhad CorpFhad International  
SBMPetro SBM LTD  
Purple Business ManagementAXN Alpha Business Management  
GainXGainX  
Alnox Busnos PeterBusnos  
Capital DAX ManagementCapital ManagermentBirech Minima CapitalAXN Alpha Business Management

 

Thank you 

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_t5_QlikView-App-Dev_How-to-fetch-all-records-from-another-table-based-on-wildmatch_td-p_1845362_Pic1.PNG

tabInput1:
LOAD RecNo() as ID1,
     Input1
FROM [https://community.qlik.com/t5/QlikView-App-Dev/How-to-fetch-all-records-from-another-table-based-on-wildmatch/td-p/1845362] (html, codepage is 1252, embedded labels, table is @1);

tabInput2:
LOAD RecNo() as ID2,
     Input2
FROM [https://community.qlik.com/t5/QlikView-App-Dev/How-to-fetch-all-records-from-another-table-based-on-wildmatch/td-p/1845362] (html, codepage is 1252, embedded labels, table is @2);

mapInput2:
Mapping
LOAD SearchString,
     '@start@'&Concat(ID2,',')&'@end@'
Group By SearchString;
LOAD SubField(Input2,' ') as SearchString,
     ID2
Resident tabInput2;

tabSearchResults:
LOAD Distinct
     *,
     'Search Result'&AutoNumber(ID1,ID2) as SearchResult;
LOAD ID1,
     SubField(ID2,',') as ID2;
LOAD ID1,
     TextBetween(MapSubString('mapInput2',Input1),'@start@','@end@',IterNo()) as ID2
Resident tabInput1
While IterNo() <= SubStringCount(MapSubString('mapInput2',Input1),'@start@');

 

hope this helps

Marco

View solution in original post

7 Replies
prahlad_infy
Creator II
Creator II
Author

@tresesco .. Sir , please help . You have helped me earlier .

prahlad_infy
Creator II
Creator II
Author

MarcoWedel

prahlad_infy
Creator II
Creator II
Author

Thank You Marco for sharing .
It definitely gave me direction to proceed .
But being amateur in Qlik  not sure how to reach to my required solution .


Following are considerations :

a) Has to be achieved as script level only 

b) number of columns Search Result1,2,3,4 .... n  ;  of OUTPUT table is not fixed as its based on search ( a particular keyword has can have more than 10 matches ) . 

Is there any other way ?

MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_t5_QlikView-App-Dev_How-to-fetch-all-records-from-another-table-based-on-wildmatch_td-p_1845362_Pic1.PNG

tabInput1:
LOAD RecNo() as ID1,
     Input1
FROM [https://community.qlik.com/t5/QlikView-App-Dev/How-to-fetch-all-records-from-another-table-based-on-wildmatch/td-p/1845362] (html, codepage is 1252, embedded labels, table is @1);

tabInput2:
LOAD RecNo() as ID2,
     Input2
FROM [https://community.qlik.com/t5/QlikView-App-Dev/How-to-fetch-all-records-from-another-table-based-on-wildmatch/td-p/1845362] (html, codepage is 1252, embedded labels, table is @2);

mapInput2:
Mapping
LOAD SearchString,
     '@start@'&Concat(ID2,',')&'@end@'
Group By SearchString;
LOAD SubField(Input2,' ') as SearchString,
     ID2
Resident tabInput2;

tabSearchResults:
LOAD Distinct
     *,
     'Search Result'&AutoNumber(ID1,ID2) as SearchResult;
LOAD ID1,
     SubField(ID2,',') as ID2;
LOAD ID1,
     TextBetween(MapSubString('mapInput2',Input1),'@start@','@end@',IterNo()) as ID2
Resident tabInput1
While IterNo() <= SubStringCount(MapSubString('mapInput2',Input1),'@start@');

 

hope this helps

Marco

prahlad_infy
Creator II
Creator II
Author

Thank You MarcoWedel , this really worked . 

MarcoWedel

you're welcome

glad it helped