count with ”WildMatch” or any other available option

[#1]

Hello all,

I have the following data:

Unique_id | sequence

123_1 | 3,0,0,1

122_1 | 3,1,0,2

111_2 | 3

100_0 | 0,0,0

I like to count how many unique ids I have for each available sequence type:

If I count the users that have “3” in their sequence I will get 3

If I count all theusers that have “1” in their sequence I will get 2

If I count all theusers that have “2” in their sequence I will get 1

I try to use “WildMatch”to give a description for each id ion the sequence, but I’m still gettingpartial count and not the enthier available results.

Appreciating yourhelp

T.

[lironbaram]

hei

attach is an example

and the load script is like this:

data:

load *,
if(WildMatch(Sequence,'*3*')>0,1,0) as Flag3,
if(WildMatch(Sequence,'*2*')>0,1,0) as Flag2,
if(WildMatch(Sequence,'*1*')>0,1,0) as Flag1;
LOAD * INLINE [
Unique_id, Sequence
123_1, "3,0,0,1"
122_1, "3,1,0,2"
111_2, 3
100_0, "0,0,0"
];

[johnw]

I'd build another table with the subsequences as rows, then just count(distinct Unique_id) for a given subsequence or subsequences.

LOAD
Unique_id
,subfield(sequence,',') as Subsequence
RESIDENT Data;