Loop condition in Transformer Stage in DS

[Palci]

I want to implement a loop in the transformer stage in datastage for a column name Col1(varchar).
Source Column = SrcCol1 (varchar)
condition is :

X =7, Y = 1
{Set n = 0
Loop (until I get a match on doing the below lookup) or (X-n = Y)
Lookup Substring (SrcCol1, 1,X-n) in a Dim1 table
n = n+1
Endloop}
If match not found then Populate "NOT FOUND" in Col1

Please help me with this loop thing !!

[qt_ky]

Where are X and Y coming from?

You could do a Transformer stage loop to output one row per substring of the source column. Loop until your X-n=Y. Substring can be done like SrcCol1[1,X-n].

After that use a Lookup stage for the Dim1 table. Set the lookup failure condition to continue which assigns NULL when not found. After that another Transformer stage to check If IsNull(Col1) Then 'NOT FOUND'...

If you need a wildcard lookup, then youcan use a Lookup stage and set the Dim1 table stage's lookup type to sparse (believe that works for DB2 and Oracle). With user-defined SQL you can change the SQL to use LIKE instead of = to do the lookup. What I've found in this case is that in the previous Transformer stage, I have to surround the column with '%' signs, as in '%':src.col1:'%'.

[Palci]

I have already calculated X and Y before in my job by using a transformer.
Can you please ellaborate more on how to mention this loop condition in the transformer stage as I am new to this tool and I'm trying this loop condition for the very first time. Thanks for your inputs.

[qt_ky]

Read about it in the Parallel Job Developer's Guide under the Transformer stage, there is a section for Specifying loops that has plenty of examples.