Macro to match values sequentially

[Nandakishore]

Hi ,

I have this requirement of matching values between two tables : 

Table 1
Ccode	Dco
A	11111
B	11121
C	11122
Table2
Ccode	Suffix	Class	Flow
A	1	C1	F1
B	11	C2	F2
C	111	C3	F3

Based on the CCode and Suffix available in table 2 need to be compared with values available in CCode and Dco of table 1 . If they are matching then bring Class and Flow to table 1 .

Can anyone propose a macro solution for this , as matching suffix is difficult for me . 

Thanks

[caltang]

Not sure what you mean by matching suffix. Are you saying that for Table 2, with value of 1, 11, and 111 - it matches to Table 1 11111, 11121, and 11122?

So, if A, then the next match is A, but 1 matches to 11111 to 1? Can you provide more explanation please?

[Nandakishore]

Hi @caltang ,

In table 2  CCode A has suffix as 1 , so in table 1 m for row with CCode A has to look for suffix 1 in DCo column . If it matches move class and flow . Then move to next  .

THanks

[mceleavey]

Hi @Nandakishore ,

I share @caltang 's confusion here and you have not provided an example of what you want to see which could possibly makes this easier, however, if you are simply trying to join on two fields, first where Ccode matches, and then where suffix is contained in Dco, then you simply need to join on Ccode then apply formulae to the required fields, in this case Class and Flow:

This then only returns the Class and Flow values for those where the Ccode matches, and the suffix is contained within Dco.

I hope this helps,

M.

[rzdodson]

Another potential solution with an iterative macro approach. Used the length of the suffix to support the filtering logic (Length = {Engine.IterationNumber=1) to test whether the ending of the DCO field matches the Suffix from Table 2.

Edit: changed the filter logic contained in the iterative macro to filter out for records where the Code field matches the Source_Code field. Reporting workflow and picture.