Filter If Exist in One of the Column

[KHSDM]

Hi all,

I have a table with the following columns:

Company	Product1	Product2	Product3	Product4	Product5
AAA	Apple	Orange	Banana	Watermelon	Avocado
BBB	Mango	Pineapple	Strawberry	Pear	Grape

What I want to do is that if the product exist in one of the "Product" column, then it is true, else false. How do I do that other than using IF statement to check every column? Will be filtering it in the script.

For example, if I filter Product = Orange, then it will return company "AAA". If I filter Product = Grape, then it will return "BBB".

[tresesco]

Try like:

If( RangeMin(Product1='ProductName', Product2='ProductName', Product3='ProductName')=-1, 'TRUE', 'FALSE') as Flag 

[KHSDM]

Thanks for the suggestion... but any alternative that make it shorter? Because in the actual data, there way more columns... a bit difficult to maintain if need to specify all the columns.

[Arthur_Fong]

Can you try cross table, then concatenating the table rows back to your original table?

Raw:
load * inline [
Company	Product1	Product2	Product3	Product4	Product5
AAA	Apple	Orange	Banana	Watermelon	Avocado
BBB	Mango	Pineapple	Strawberry	Pear	Grape
](delimiter is '	');

CrossTable(Product,Type)
Data:
load * 
resident Raw;



//NoConcatenate
left join(Raw)
Data2:
load *,if(wildmatch(Type,'*Orange*'),'True','False')AS Status;
load Company,Concat(Type,',')as Type,concat(Product,',')as Product
resident Data
group by Company;

drop table Data;

[KHSDM]

Hi Arthur,

The data model contain more than one "dimension". Other than "product", it also has "Time", "location", etc. So crosstable not really workable.

[Arthur_Fong]

The way I can think of is using concatenated wildmatch:

where WildMatch(Product1&Product2&Product3&Product4&Product5,'*Grape*');

[KHSDM]

Hi Arthur,

I also tried similar method... but there is one issue. If any of the column have similar name, then it will be filtered as well.

For example, I want filter "Grape". But using wildmatch, it will also include "Grapefruit".

[Arthur_Fong]

Maybe try adding another and condition?

where WildMatch(Product1&Product2&Product3&Product4&Product5,'*Grape*')
and
not WildMatch(Product1&Product2&Product3&Product4&Product5,'*GrapeFruit*');

[KHSDM · Accepted solution]

Aw... I wont know what are the items to exclude. But this give me an idea:-

LOAD
    *
where
WildMatch(Product, 'Grape_*', '*_Grape', '*_Grape_*', 'Grape')
;
LOAD
Company,
Product1 &'_'& Product2 & '_' & Product3 & '_' & Product4 & '_' & Product5 as Product
Product1,
Product2,
Product3,
Product4,
Product5
resident Data;