Using of MapSubstring for particular part of the string

[Peony]

Hi all.

I  have  two tables and need to compare data between. I'm doing the next steps.

1. Taking field from the catalog. 

Preload:
Load Prefix
From catalog.qvd (qvd);

MapPrefix:
Mapping LOAD
Prefix,
'/'& Prefix &'\'
Resident Preload;

2. Next step I'm loading data to compare and using MapSubstring to get the result of compare.  

Calls:
Load *,
TextBetween(MapSubString('MapPrefix' , ValueToCompare), '/', '\') as Result

From ValueToCompare.qvd (qvd);

3. As the result table I received something like that:

Prefix	ValueToCompare
5066	50662911
5066	5066297
5066	506639
5067	50671268888
5067	50671388888
5068	50683391369
5068	506838684
79	5244202579
8458	5258884587

And last two rows show me not that result that I need. The thing is that compare should be applied only at the beginning of the ValueToCompare, not in the middle or in the end. 

And I don't understand how to apply comparison only for beginning of the value? 

[sunny_talwar · Accepted solution]

Try something like this

MapPrefix:
Mapping
LOAD '@' & Prefix,
	 '/'& Prefix &'\';
LOAD * INLINE [
    Prefix
    5066
    5066
    5066
    5067
    5067
    5068
    5068
    79
    845
];

Calls:
LOAD *,
	 TextBetween(MapSubString('MapPrefix' , '@' & ValueToCompare), '/', '\') as Result;
LOAD * INLINE [
    ValueToCompare
    50662911
    5066297
    506639
    50671268888
    50671388888
    50683391369
    506838684
    5244202579
    5258884587
];

[Peony]

Great idea, Sunny! Thank you much for it!🤗