How to combine values as one column?

[#1]

Hi,

I have two column in excel. CustomerId, Customername, CustomerStatus and Owner. I created a pivot table with these columns. All I need is count of CustomerId when CustomerStatus= Fail1 ,Pass1 and column name as App

count of CustomerId when CustomerStatus= Fail2,Promote and column name as Rej

Please can anyone help me how to do this. I used Mix match in script. But it is not working.

Thanks.

[#2]

Please see attached.

[MayilVahanan]

Hi

Try like this

Load CustomerId, Count(CustomerId) as App From Tablename Where

WildMatch(CustomerStatus,'Fail1' ,'Pass1') Group by CustomerId;

Load CustomerId, Count(CustomerId) as Rej From Tablename Where WildMatch(CustomerStatus,'Fail2' ,'Promote') Group by CustomerId;

[MK_QSL]

Instead of below two in Script

     If(MixMatch(CustomerStatus,'Fail1','Pass1'),CustomerStatus)  as App,

     If(MixMatch(CustomerStatus,'Fail2','Promote'),CustomerStatus) as Rej,

use a single expression as

If(MixMatch(CustomerStatus,'Fail1','Pass1'),'App', 'Rej') as Flag,

Now Remove App and Rej from the Pivot table and Use Flag instead...

Hope this helps..

[#5]

Thanks. for two values it is working. how can I change this script if I have more than two values.

I,e actually I have in data as below

Fail1

Fail2

Pass1

Promote

Promote1

Promote2

Promote3

Promote4

Promote5

Promote6464

Promote56456

Promote23

Promote24

Stamp

Stamp1

Stamp2

Stamp34

Stamp56

Stamp12

Stamp34

I want

Fail1

Fail2

Pass1

Promote

Promote1

Promote2

Promote3

Promote4

Stamp2

Stamp34   as one group

and

Promote5

Promote23   as second group

and

Promote6464

Promote56456

Stamp34

Stamp56

Stamp12

Stamp34

Promote24   as other group.

Please help me how to change this.

[MK_QSL · Accepted solution]

Use Nested If function...

IF(Match(YourFieldName,'Fail1','Fail2','Pass1','Promote' etc etc.........),'Name1',

IF(Match(YourFieldName,'Promote5','Promote23'), 'Name2'),

'Name3')) as Flag

UPDATE: FULL CODE

IF(Match(FieldName,'Promote5','Promote23'), 'Name3',

  IF(Match(FieldName,'Promote6464','Promote56456','Stamp34','Stamp56','Stamp12','Stamp34','Promote24'),'Name2', 'Name1')) as Flag

[#7]

Thanks. That's working.