Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
Does any body have a example or idea for...
Basically I need to find not a single substring, but search among a list with multiples substrings…
So that when I would be running script for TABLE 2 I would like to find the codes listed in TABLE 1 in the field called text.
The codes into de field called text does not follow any pattern.... such as between ( ), " " etc...That's why I need to look individually for all the codes into de field text.
TABLE 1:
Code
CAD12
DDD25
B6D60
CDD12
4DD40
TABLE 2:
User Text
Alex Random text /CDD12/ random text
Rudolf Another random text *CAD12* random text
Paul Other text (DDD25) text
John Text 4DD40 random text
The result expected
User Text Code
Alex Random text /CDD12/ random text CDD12
Rudolf Another random text *CAD12* random text CAD12
Paul Other text (DDD25) text DDD25
John Text 4DD40 random text 4DD40
I thought about these functions (FindOneOf or substringcount ) but I not so sure..
Table:
LOAD
[user],
FindOneOf([Text],[Code],0)
substringcount([Text],
)
Any ideas or examples?
THANKS!
Give this a shot....
mid(Text,index(Text,'CDD'),5) as 'Code'
That's only gonna work if all of the codes are CDD## which the 5 you posted are so maybe that will be fine.
I really appreciate your help, but the Code table does not have a pattern also..
Code
CDD12
CDD25
CDD60
CDD12
CDD40
So the code does not always begin with CDD nor necessarily have 5 digits.
Do you have any other idea?
Someone else has?
Thanks
Well in the example you just posted again all of the codes start with CDD and have 2 numbers so... show me one that is different b/c I think one of us is misunderstanding and I'm not sure who that is yet.
I'm really sorry for the example given.
I have just edited the original topic
I think now it would be easier to understand what I meant wiht Code table does not have a pattern also..
Thanks!
Yeah that makes it a lot harder...
I have to figure there is some way to do this with peek and a for loop but that could be a nightmare if you have a LOT of data. Maybe try something like this...
For i = 1 to NoOfRows('Table1')
LET vCode=peek('Table1', 1-$(i),'Code');
LOAD
User,
Text,
$(vCode) as 'Code'
INLINE [
inline stuff goes here
]
WHERE index(Text,$(vCode))>0;
Next i
I think your best bet take a couple of steps, what you'll want to do is load all the strings into a single variable and then use the wildmatch() function to figure out what string has the match. So I'll try to modify your example to illustrate.
//Load the codes into your table adding a record number and asterics on the code so you can use the
//wildmatch function later
TABLE1:
Load * Inline [
Record, Code
1, *CAD12*
2, *DDD25*
3, *B6D60*
4, *CDD12*
5, *4DD40*
];
//Now in a temporary table you can concatenate the codes enclosed in single quotes and delimited with //commas
CodesTemp:
Load
Concat(chr(39) & Code & chr(39), ',') as Codes
Resident TABLE1;
//Store the concatenated code list into a variable
Let vCodeList = Peek('Codes',0,'CodesTemp');
//The wildmatch function returns a number indicating the position of the matching list item
TABLE2:
Load
User,
Text,
WildMatch(Text,$(vCodeList)) as Record;
Load * Inline [
User, Text
Alex, Random text /CDD12/ random text
Rudolf, Another random text *CAD12* random text
Paul, Other text (DDD25) text
John, Text 4DD40 random text
];
//Finally join the table and clean up.
Left Join (TABLE2)
Load * Resident TABLE1;
//Clean up
Drop Tables TABLE1, CodesTemp;