topic How to combine values as one column? in QlikView

How to combine values as one column?

Thu, 18 Sep 2014 09:48:20 GMT

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.

Re: How to combine values in as one column?

Thu, 18 Sep 2014 09:48:40 GMT

Please see attached.

Re: How to combine values in as one column?

MayilVahanan — Thu, 18 Sep 2014 09:57:19 GMT

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;

Re: How to combine values as one column?

MK_QSL — Thu, 18 Sep 2014 10:01:17 GMT

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..

Re: How to combine values as one column?

Thu, 18 Sep 2014 10:14:07 GMT

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.

Re: How to combine values as one column?

MK_QSL — Thu, 18 Sep 2014 10:18:53 GMT

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

Re: How to combine values as one column?

Thu, 18 Sep 2014 10:38:05 GMT

Thanks. That's working.