topic Re: Repeat a value in dimension in QlikView

Repeat a value in dimension

Mon, 17 Aug 2015 11:34:33 GMT

Hi,

I have such problem: I have a fact table in my database which has object_id, type, date and value. I have also two dimensions: Clients and Products which has fields object_id, type, client_id/product_id, client_name/product_name.

In my script it looks like this:Products:

Products:
SELECT object_id
     object_id as product_id,
     name as product_name,
     'P' as type
from Products


Clients:
SELECT object_id
     object_id as client_id,
     name as client_name,
     'C' as type
from Clients

Data:
SELECT Date,
     object_id,
     value,
     'P' as type
from Products_data
where objects_id in (select object_id from Products)
Concatenate:
SELECT Date,
     object_id,
     value,
     'C' as type
from Clients_data
where object_id in (select object_id from Clients)

Then I have chart table:

client_id      sum(value)      sum({<type={"P"}>} value)
K1               210               0
K2               124               0            
K3               200               0
K4               156               0
-                 35              35

Is there any possibility to show 35 instead of zeros? I mean to repeat 'the other' value (in this case 35) on every row with not null client value which doesn't match condition in expression?

Thanks in advance!

Re: Repeat a value in dimension

sunny_talwar — Mon, 17 Aug 2015 11:35:27 GMT

Try this expression:

=Sum(TOTAL {<type={"P"}>} value)

Re: Repeat a value in dimension

Mon, 17 Aug 2015 11:37:42 GMT

You are great, it works!

Re: Repeat a value in dimension

sunny_talwar — Mon, 17 Aug 2015 11:45:16 GMT

Awesome

I am glad it worked.

Best,

Sunny

Re: Repeat a value in dimension

Mon, 17 Aug 2015 13:48:57 GMT

I have one more question. In this case if I would have also client K5 who would have NULL value (it wouldn't be in Data table), then (when I use TOTAL in expression) it would show up in table. What should I do if I don't want to see these clients (with sum(value)=0, but sum(TOTAL {<type={"P"}>} value)=35)?

Re: Repeat a value in dimension

sunny_talwar — Mon, 17 Aug 2015 14:03:09 GMT

May be this:

If(Sum(Value) = 0, Sum(TOTAL {<type={"P"}>} value))

If(Sum(Value) > 0, Sum(TOTAL {<type={"P"}>} value))

Re: Repeat a value in dimension

Tue, 18 Aug 2015 05:46:49 GMT

It helped to find the finally expression:

If(Sum(Value) != 0, Sum(TOTAL {<type={"P"}>} value)).