Data Insight - Compare parameters

[adrian_storen]

Hi all,

I'm trying to use a parameter to inject into a regex pattern and also via other means but having no luck.  Any ideas?

I used the lookup function in a rule to return a column that has comma separated values. The value in the table has no inverted commas so I assume this function returns data like:

$type_list = 'AA,BB,CC'

I have a second variable with as $type (eg equals AA)

The rule in this case was: IF ($type IN ($type_list) then pass else fail.  This almost works, well it worked on most data but returned passed records as failed as I think it does not see each as separate values.

So I tried to replace the commas with ',' but this errors and I could not use escape characters to change it.

Next I tried to replace the IN with LIKE.  This normally works, despite not being in the User Guide when hard coding values, but I could not add the %% on either side of the parameter to get it to work.  I thought this might work: $type LIKE '%' || $type_list || '%' and other similar combinations.  However, this does not work or does not validate.

So lastly I tried to use a REGEX pattern.  First I tried

match_regex($type_list,$type,null);

But this did not work.  So I tried adding extra regex characters and the like, but Data Insight does not seem to like using parameters as part of a regex, from work I could see.  I'm not sure if this is a bug or not, as I tried hard coding text and it still does not like it.

I've excluded all the complex parts of the rule to get this basic check working without luck.

Does anyone have any ideas?

regards

Adrian

[former_member187605 · Accepted solution]

BEGIN

   RETURN length(replace_substr($TypeList, $Type, '', 1, 1)) != length($TypeList) ;

END

[adrian_storen · Accepted solution]

Thanks Dirk.  That's very simple and elegant... and it worked!

I am a little concerned with utilising parameters inside the REGEX in DI, as I'm not sure if this is a bug or not, but for now I do not need to worry, and cannot validate either way.

regards

Adrian