Determine value with partial sequence of Path field.

[Foesi12]

Hi

I have a table with two columns Factor and Path.
In the third column "Value" the determined value of the field Factor should be related.
The graphic should describe my problem in more detail.

Kind regards
Fösi

Temp:
LOAD *
Inline [
factor, path,
3, 1|11
4, 1|22
3, 1|66
8, 1|44
7, 1|77
3, 1|11|111
2, 1|22|222
7, 1|66|666
7, 1|44|444
8, 1|77|888
2, 1|11|111|3333
7, 1|22|222|4444
9, 1|66|666|7777
5, 1|44|444|8888
3, 1|77|888|9999
1, 2|11
7, 2|99
3, 2|77
2, 2|11|123
2, 2|99|234
3, 2|77|789
8, 2|11|123|8
5, 2|99|234|7
7, 2|77|789|7
]
(delimiter is ',');

[marcus_sommer]

The match-logic isn't quite clear for me and probably more complex as indicated in the example. Therefore just a few hints how you may approach to your target.

Quite common by evaluating such concatenated strings respectively arrays is to split them into single values respectively smaller arrays in regard to their belonging keys and other fields and then using them in mappings to return the wanted matches.

A single simple mapping-table might be not enough to get the intended results but you may apply n mappings as well as nested ones (the mapping-table itself as well as by calling the mapping). Important by nested approaches is the order of the data and executions - especially if multiples matches are possible. By multiple matches you may beside an applymap() call also use a mapsubstring() approach.

Here an approach how to resolve data like yours and with the included meta-data you could always track which data was on which point transformed. Further useful may be to add len() and substringcount() as well as any kind of rank() or flag/scoring-values to support your match-logic:

Temp:
load *, rowno() as RowNo where IterNo1 * IterNo2 > 1;
load *, subfield(SubPath1 & '|', '|', iterno()) as SubPath2, iterno() as IterNo2
   while iterno() <= substringcount(SubPath1 & '|', '|');
load *, text(left(path & '|', index(path & '|', '|', -iterno()) - 1)) as SubPath1, iterno() as IterNo1
   while iterno() <= substringcount(path, '|') + 1;
load *, recno() as RecNo
Inline [factor, path,
....] (delimiter is ',');