Flag category according to whether a particular value exists in the category

[sifatnabil]

Hi all,

I have a data set of single orders aggregated by categories like A, B, C. If a particular value exists in the Name field, I want to flag the entire category. The raw table looks like this:

Category, Name

A, John

A, Bob

A, Tim

A, Jack

B, John

B, Bob

B, Tim

B, Nathan

Since category B contains Nathan, I want to flag the entire category B as Type "EM", whereas category A should be "not EM". How could I do this? Thanks.

[Oleg_Troyansky · Accepted solution]

There might be more elegant solutions, but the one that comes to my mind is this:

Aggregate the data and concatenate all the Names per category in a single field. Then check if the concatenated string contains your value:

LOAD

     *,

     if(index('Nathan', Names) > 0, 'EM', 'not 'EM') as _EM_Flag

;

LOAD

     Category,

    concat(Name, ',') as Names

resident

     RawData

GROUP BY

     Category

;

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!

[sifatnabil]

Thanks, this works. I have an entire list of names that I want to flag, so I used wildmatch(Names,'*Name1*','*Name2*',etc.)