topic Re: Calculated dimension in QlikView

Calculated dimension

stabben23 — Tue, 01 Apr 2014 15:39:41 GMT

Hi,

I need help, I want to reduce values in my dimension based on another dimension.

Lets say that I have Accounts 100, 101, 102 ,200, 201, 202.

These Accounts is grouped like A=100,101, B=200,201

Now I want to show Accounts based on Group A and B.

This will gives me Account 100, 101, 200, 201 in my dimension Account.

Re: Calculated dimension

giakoum — Tue, 01 Apr 2014 15:41:49 GMT

Then you need to have a grouping field, a table like this:

Account, AccountGroup

100, A

101, A

102, A

200, B

201, B

etc

and filter on AccountGroup

Re: Calculated dimension

its_anandrjs — Tue, 01 Apr 2014 15:46:51 GMT

Make a calculated dimension in your load script like

LOAD * Inline

[

Accounts

100

101

102

200

201

202

];

Main:

LOAD

if(Match(Accounts,100,101,102),'Group A',

if(Match(Accounts,200,201,202),'Group B')) as GroupAccount

Resident A;

DROP Table A;

Hope this helps

Re: Calculated dimension

stabben23 — Tue, 01 Apr 2014 15:47:14 GMT

I have, and this is in a pivot table.

I need to use calculated dimension.

Re: Calculated dimension

giakoum — Tue, 01 Apr 2014 15:49:35 GMT

I don't understand. if you have it, then why don't you use in the dimension? Why do you need a calculated one?

Re: Calculated dimension

stabben23 — Tue, 01 Apr 2014 15:49:57 GMT

This is not script issue, I have all tables and the grouping already.

Re: Calculated dimension

MK_QSL — Tue, 01 Apr 2014 15:50:53 GMT

You can use as below...

Load

Account,

IF(Left(Account,1)=1,'A','B') as Group

Inline

[

Account

101

102

103

201

202

203

];

Re: Calculated dimension

stabben23 — Tue, 01 Apr 2014 15:51:18 GMT

Because I dont want to se 102 and 202 in my dimension.

Re: Calculated dimension

giakoum — Tue, 01 Apr 2014 15:52:42 GMT

Then use the grouping field as dimension.

Re: Calculated dimension

MK_QSL — Tue, 01 Apr 2014 15:52:50 GMT

What is the exact required output you want?

can you be more specific please !

Re: Re: Calculated dimension

stabben23 — Tue, 01 Apr 2014 15:59:46 GMT

This is my table and I dont want to se Account that dont have a Group.

Re: Re: Calculated dimension

giakoum — Tue, 01 Apr 2014 16:03:11 GMT

if you use only Group as a dimension and check Suppress null you will only see values with Group.

if you want to Account as Dimension, the create a calculated dimension : If(not isNull(Group), Account)

Re: Calculated dimension

MK_QSL — Tue, 01 Apr 2014 16:05:56 GMT

If you have selected Group in dimension, tick Suppress when value is null..

If Group is not in your dimension....

Use below as calculated dimension

IF(Len(TRIM(Group))=0,Account

Re: Re: Calculated dimension

stabben23 — Tue, 01 Apr 2014 16:10:21 GMT

Hi Ioannis,

Yes this solve my problem, I use Account as a first Dimension and Group as second. Thats why I need to "hide" null in Group filed. When I use Group as first Dimension there is no problems. I also need to suppress null.

Thanks

Re: Re: Calculated dimension

its_anandrjs — Tue, 01 Apr 2014 16:13:34 GMT

Use suppress null value then it removes the blank values and only view the value which is not blank value.

Hope this helps