Wildmatch with two criteria

[Anonymous]

Hey all

So I have the following: (if (WildMatch(PROD_GROUP, '*Mud*','*Salt*'),'Mud and Salt',null)), with the measure being count(customers).

The issue I have is that it is returning the count of the total number of customers who have Mud, and the total number who have Salt.

What I want is the number of customers who have both products.

Any help please?

Thanks

[tresesco]

Try like:

Dimension: PROD_GROUP

Exp: count({<PROD_GROUP={'*Mud*'}>*<PROD_GROUP={'*Salt*'}>}customers)

[prat1507]

Hi

Change it to

(if (WildMatch(PROD_GROUP, '*Mud*')>0 and WildMatch(PROD_GROUP, '*Salt*')>0),'Mud and Salt',null)),

Regards

Pratyush

[Chanty4u]

hi try this its working

aa:

LOAD * INLINE [

    Record, Date, Year ,Product

    abc, 7/9/2015, 2015,salt

    bc, 6/8/2015, 2015,sugar

    aabc, 4/1/2015, 2015,salt

    adbc, 12/3/2016, 2016,sugar

    abgc, 16/2/2016, 2016,rice

    abcv, 6/1/2016, 2016,sugar

    abcn, , 2017,salt

    abcj, , 2017,sugar

]

;

LOAD

if(WildMatch(Product,'*sa*','sugar'),'Mud and Salt','null') as result

Resident aa;

[jonathandienst · Accepted solution]

You mean you want an AND rather than an OR between the criteria? I assume that you have multiple rows for each customer, so that one row could contain Mud and the other Salt? If the previous suggestions don't work for you, try the below.

I think that this is syntactically correct (intersection of customers with *mud* AND *salt*):

Count({<customers = P({<PROD_GROUP = {'*Mud*'}>})> * <customers = P({<PROD_GROUP = {'*Salt*'}>})>} customers)

You may need a DISTINCT:

Count({<customers = P({<PROD_GROUP = {'*Mud*'}>})> * <customers = P({<PROD_GROUP = {'*Salt*'}>})>} DISTINCT customers)

[jonathandienst]

In a calculated dimension:

Aggr(Only({<customers = P({<PROD_GROUP = {'*Mud*'}>})> * <customers = P({<PROD_GROUP = {'*Salt*'}>})>} customers), customers)

But I would avoid that construct unless you have a very small data set. Rather add a derived 'Mud and Salt' field during the load.

[Anonymous]

Thank you for the help (and to the other repliers as well!)