topic Re: Exclude sequence patterns in QlikView

Exclude sequence patterns

sujit_nath — Wed, 11 Apr 2018 13:21:50 GMT

Hi,

I have data set such as:

Table:

ID	Value
[1][2][3][4]	E\|E\|E\|
[3][2][1]	E\|E\|E
[2][1]	E\|E
[1][3]	E\|E
[3][7]	E\|
[7][2][1]	\|E\|E
[2][1][4]	E\|E\|

I need to exclude the Bold rows in the next table where I load them.

The logic behind this is: I need to exclude the combinations where I see [1] , [2] or [3]

if it is [1] in ID, I'm adding E as value.

the data is concatenated thereafter.

So, a value of [1][4][5] will have values as : E||

Again, coming back to the original ques, I need to exclude all those ID's where Value starts and ends with E and there is no || (double pipe) in between.

Could anyone suggest a process to do so? Help would be much appreciated.

Re: Exclude sequence patterns

Anil_Babu_Samineni — Wed, 11 Apr 2018 13:41:38 GMT

How you want to see in second table after that?

Re: Exclude sequence patterns

sujit_nath — Wed, 11 Apr 2018 13:43:23 GMT

In the second table I simply need to load the ID's that are not bold. I'll only use the value field to decide which ID's to include.

Re: Exclude sequence patterns

Anil_Babu_Samineni — Wed, 11 Apr 2018 13:48:39 GMT

Does this helps in your case?

Sample:

Load ID, Value From Sample;

Final:

NoConcatenate

Load ID, Value Resident Sample Where Not WildMatch(ID, '[3][2][1]', '[2][1]', '[1][3]');

Re: Exclude sequence patterns

sujit_nath — Wed, 11 Apr 2018 16:23:54 GMT

Thanks Anil, but I forgot to mention.. this is a sample data. I need to put some generic rules in the where condition which would pick up these.

Re: Exclude sequence patterns

boorgura — Wed, 11 Apr 2018 18:55:06 GMT

Your where clause would be on the lines of:

where not ((left(Value,1) = 'E' and right(Value, 1) = 'E' and WildMatch(Value, '*||*') = 0));

That is - trying to exclude the record where Value starts and ends with E and as well there are no occurances of ||

Re: Exclude sequence patterns

MarcoWedel — Wed, 11 Apr 2018 23:30:37 GMT

Hi,

one example:

table1:
LOAD ID,
    Value
FROM [https://community.qlik.com/thread/297878] (html, codepage is 1252, embedded labels, table is @1)
Where not Value like 'E*E' or Value like '*||*';

hope this helps

regards

Marco

Re: Exclude sequence patterns

sujit_nath — Thu, 12 Apr 2018 06:18:18 GMT

Perfect!! This would help me

Re: Exclude sequence patterns

MarcoWedel — Thu, 12 Apr 2018 18:20:11 GMT

Thanks, I'm glad it worked for you.

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

Re: Exclude sequence patterns

sujit_nath — Fri, 13 Apr 2018 11:51:42 GMT

How do I close this thread Marco? Apologies for the silly ques

Re: Exclude sequence patterns

big_dreams — Fri, 13 Apr 2018 11:53:13 GMT

You already close the thread, by selecting answer as correct.

Regards,

Re: Exclude sequence patterns

sujit_nath — Fri, 13 Apr 2018 12:04:19 GMT

Cool!! Thanks.. Didn't know this.. BTW isn't there a app for this Qlik community?