How to exclude possible combinations of few keys?

[sujit_nath]

I have data set such as:

Key

I need to exclude only the highlighted ones. ,, or any combination containing these.

If these are shared with others, these should not be excluded.

I guess wildmatch/match is not possible in this scenario.

Is there a way we can achieve this?

[sujit_nath]

Hi Gerold,

This would return no results as it would try to do the exact match. and square braces have no significance here.

Even if I modify the logic mentioned, what about FED, EF etc. so, difficult to write all possible combinations. In actual data it would be huge.

[gerry_hdm]

using in the script

where not ......

[gerry_hdm]

in the Row 5 you have and

is not  highlighted?

[sujit_nath]

Yes,

Aplologies for missing the square braces. but as I mentioned, I want to exclude all combinations that can be formed with ,,. In my actual data it won't be just alphabets, but even larger set of strings.

I know what combinations to exclude. Like: , etc. should be included. This is the reason I can not use wildmatch.

Also, Match won't be a feasible solution as taking out all permutations of a large data set would be practically impossible. Even if the key contains few hundred records.

[gerry_hdm]

if(findOneOf(KEY,D,E,F) =0

greetings gerold

[tresesco]

Hi sujit_nath‌

An alternate way I can think of, like:

Where Len(Replace(Replace(Replace(<String>, 'D', ''), 'E', ''), 'F',''))>0

For more characters, you have to increase the replace() accordingly.

[sujit_nath]

Seems interesting! I'll check and get back on this

[sasiparupudi1]

Maybe like attached?

[dominicmander]

Do you have a finite number of keys that can be in any one record? (e.g. a record is never more than 3 keys long for example?

If I understand right, you want to be able to exclude any key which contains only values present in your list of keys to exclude.

If so, how about the following? - we flag each key in the composite key that is present in the list of keys to be excluded. If the sum of those is the same as the number of keys in the composite key then the composite key is only composed of keys to be excluded so we flag it for exclusion.

keys_to_exclude:

load * inline [

exclude

D

E

F

];

data:

load * inline [

key

A|B|C

D|E|F

F|E

E|D

D|F|G

G|H|I

A|G|M

B

E

A|K

B|M|K

];

temp_data:

load

key,

if(SubStringCount(key,'|')+1 = key1_flag+key2_flag+key3_flag, 1, 0) as exclude_flag

;

load

key,

if(exists(exclude,subfield(key,'|',1)),1,0) as key1_flag,

if(exists(exclude,subfield(key,'|',2)),1,0) as key2_flag,

if(exists(exclude,subfield(key,'|',3)),1,0) as key3_flag

resident

data

;