topic Re: How to merge categories in dimension and pick it?(hurry) in QlikView

How to merge categories in dimension and pick it,not in script?(hurry)

uacg0009 — Mon, 24 Mar 2014 08:51:01 GMT

Hi all,

I have data like :

Now I want to merge the CATE to new category.

the new cate would be:

PPP=A,B,C

SSS=AA,BB,CC

DDD=AAA,BBB,CCC

Names(PPP,DDD,SSS) are not important I thiink, because I want to calculate,

how many vips buy only one new cate, how many vips buy more than one new cate.

The result should be:

I hope I have clarified what I want, and if you have any further question please feel free to contact me.

Thank you so much.

Re: How to merge categories in dimension and pick it?(hurry)

alexandros17 — Mon, 24 Mar 2014 08:57:26 GMT

when you load your data do something like:

load

...

If(cate='A' or cate='B' or cate='C', 'PPP',

If(cate='AA' or cate='BB' or cate='CC', 'SSS',

If(cate='AAA' or cate='BBB' or cate='CCC', 'DDD',

'---'

)

) as myField

and use this new field as dimension

Re: How to merge categories in dimension and pick it?(hurry)

sushil353 — Mon, 24 Mar 2014 09:07:06 GMT

Hi,

You can create new CATE using below expression:

load

...

if(len(CATE)=1,'PPP',if(len(CATE)=2,'SSS','DDD')) as NewCATE,

...

HTH

Sushil

Re: How to merge categories in dimension and pick it?(hurry)

uacg0009 — Mon, 24 Mar 2014 10:44:01 GMT

Hi Alessandro,

Thanks for your reply, but i want to finish it in dimension, not in script, do you have any idea to solve it?

Re: How to merge categories in dimension and pick it?(hurry)

uacg0009 — Mon, 24 Mar 2014 10:48:03 GMT

Hi Sushil,

Could you have any idea to solve this in dimension, not in script?

Thank you for your reply.

Re: How to merge categories in dimension and pick it?(hurry)

alexandros17 — Mon, 24 Mar 2014 10:48:52 GMT

use this

If(cate='A' or cate='B' or cate='C', 'PPP',

If(cate='AA' or cate='BB' or cate='CC', 'SSS',

If(cate='AAA' or cate='BBB' or cate='CCC', 'DDD',

'---'

)

as dimension, it works

Re: How to merge categories in dimension and pick it,not in script?(hurry)

uacg0009 — Mon, 24 Mar 2014 10:55:20 GMT

I want to calculate,how many vips buy only one new cate, how many vips buy more than one new cate.

Not just output PPP SSS DDD, do you have any idea?

Re: How to merge categories in dimension and pick it,not in script?(hurry)

Colin-Albert — Mon, 24 Mar 2014 11:04:28 GMT

You can use the expressions given by Alessandro in a calculated dimension, but it is much more efficient to do calculations like this in the script.

Re: How to merge categories in dimension and pick it,not in script?(hurry)

uacg0009 — Mon, 24 Mar 2014 11:15:38 GMT

I want to calculate,how many vips buy only one new cate, how many vips buy more than one new cate.

Not just output PPP SSS DDD, Could you please clarify how to do that using Alessandro's expressions?