topic Re: How to exclude possible combinations of few keys? in QlikView

How to exclude possible combinations of few keys?

sujit_nath — Tue, 03 Apr 2018 09:40:29 GMT

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?

Re: How to exclude possible combinations of few keys?

jaumecf23 — Tue, 03 Apr 2018 09:54:00 GMT

Try this:

Where not(WildMatch(Key,'**','**','**'));

Re: How to exclude possible combinations of few keys?

sujit_nath — Tue, 03 Apr 2018 10:04:06 GMT

Thanks Jaume, but this would eliminate , etc. as well, which is not what I want.

Re: How to exclude possible combinations of few keys?

tresB — Tue, 03 Apr 2018 10:11:45 GMT

You can try like:

Where not FindOneOf(Key, 'ABCGHIJKLMNOPQRSTUVWXYZ'); // excluding DEF

Re: How to exclude possible combinations of few keys?

sujit_nath — Tue, 03 Apr 2018 10:22:10 GMT

Thanks Tresesco, but A,B,C.. what I mentioned would not actually be alphabets in my data. Also, I won't know these data as these would change frequently. What I have is the values/combinations that need to be excluded (the values would be large though)

Taking these into consideration, the FindOneOf logic won't work.

Re: How to exclude possible combinations of few keys?

tresB — Tue, 03 Apr 2018 10:23:57 GMT

You might have to include other possible characters as well in the findoneof() like:

Where not FindOneOf(Key, 'ABCGHIJKLMNOPQRSTUVWXYZ!@#$%abcd...'); // excluding DEF

Re: How to exclude possible combinations of few keys?

jaibau1993 — Tue, 03 Apr 2018 10:25:07 GMT

What about brute force?

Where not(WildMatch(Key,, , , , , , , , , , , , , , ));

Whit only 3 keys (as in your problem) you can do it in a finite time and it should work (not beautiful but works).

I'll let you know if I find a better way to do it.

Jaime.

Re: How to exclude possible combinations of few keys?

tresB — Tue, 03 Apr 2018 10:30:07 GMT

If you need more dynamic solution, you might have to try something like explained here:

How to use regular expressions

Regular expression [A-Z] *

Re: How to exclude possible combinations of few keys?

sujit_nath — Wed, 04 Apr 2018 09:32:19 GMT

Hi Tresesco,

This does not work as expected. I want to exclude all combinations that can be formed from 'D','E','F' {D,E,F,DE,ED,EF,FE,DF,FD,DEF,DFE,EDF,EFD,FDE,FED}

Though actual data would be large enough.

Re: How to exclude possible combinations of few keys?

gerry_hdm — Wed, 04 Apr 2018 10:28:54 GMT

where not match(Key,,,, )

Re: How to exclude possible combinations of few keys?

sujit_nath — Wed, 04 Apr 2018 10:35:28 GMT

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.

Re: How to exclude possible combinations of few keys?

gerry_hdm — Wed, 04 Apr 2018 10:38:35 GMT

using in the script

where not ......

Re: How to exclude possible combinations of few keys?

gerry_hdm — Wed, 04 Apr 2018 10:41:06 GMT

in the Row 5 you have and

is not highlighted?

Re: How to exclude possible combinations of few keys?

sujit_nath — Wed, 04 Apr 2018 10:46:06 GMT

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.

Re: How to exclude possible combinations of few keys?

gerry_hdm — Wed, 04 Apr 2018 10:50:01 GMT

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

greetings gerold

Re: How to exclude possible combinations of few keys?

tresB — Wed, 04 Apr 2018 10:58:22 GMT

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.

Re: How to exclude possible combinations of few keys?

sujit_nath — Wed, 04 Apr 2018 11:19:43 GMT

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

Re: How to exclude possible combinations of few keys?

sasiparupudi1 — Wed, 04 Apr 2018 11:46:02 GMT

Maybe like attached?

Re: How to exclude possible combinations of few keys?

dominicmander — Wed, 04 Apr 2018 13:09:08 GMT

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

];

data:

load * inline [

key

A|B|C

D|E|F

F|E

E|D

D|F|G

G|H|I

A|G|M

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

;