topic Re: Multiple Fact Tables in QlikView

Multiple Fact Tables

Sun, 14 Apr 2013 12:37:21 GMT

Hi everyone,

I've got a document with 4 facts table. There are dependent from each others.

Table 1 = Requests.

Each request can generate one or more Trip (Table 2).

A trip can be comosed of one or more services (Table 3) (but services can also exist without being linked to a trip neither to a request).

A Service can have one or more assignment.

I've already managed to make a single fact table but I don't know how to manage each mesures.

My Full fact table seems like

Request ID Request Date Trip ID Trip Date Service ID Service Date Assign.ID Assign Date Measure Service

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 01/01/2012 1 02/01/2012 1 02/01/2012 1 02/01/2012 10

1 01/01/2012 1 02/01/2012 2 02/01/2012 2 02/01/2012 20

1 01/01/2012 1 02/01/2012 2 02/01/2012 3 02/01/2012 20

- - - - 3 02/01/2012 4 02/01/2012 30

2 01/01/2012 2 02/01/2012 4 02/01/2012 5 02/01/2012 40

My question is for the request 1 - service 2.

If i make sum on the service measure I wil have 50. What I want is 30 because 2 is the same service and it is duplicate because it is composed of 2 assignements but it should be counted only once.

How do you manage that?

I think using Aggr but I don't really understand how it works and I could use some help.

Thanks

caro.

Re: Multiple Fact Tables

Jason_Michaelides — Mon, 15 Apr 2013 10:17:41 GMT

Try:

Aggr(Sum([Measure Service]),[Request ID]) or you may need the DISTINCT qualifier in there:

Aggr(DISTINCT Sum([Measure Service]),[Request ID])

Hope this helps,

Jason

Re: Multiple Fact Tables

Wed, 17 Apr 2013 11:22:00 GMT

Hi Jason,

I don't really understand how the aggr works.

Could you explain it to me?

I know the distinct. Without the aggr(), I could use it for [Request ID] 1 and it would work but then if I don't select the request ID and I have 2 time the same measures for 2 different request ID, then I don't want the distinct on it.

Can Aggr solve this?

Thanks for your help

Caro.

Re: Multiple Fact Tables

Wed, 17 Apr 2013 13:19:01 GMT

I will explain it in my cowboy way

Aggr ignores dimensions. So if you want an expression to calulate using dimensions that aren't present in your table or you want to calculate something using only the first 2 dimensions in your report and not all 3, you would use aggr. Essentially you can build an entire straight table with no dimensions using aggr and still get the values you want. It is a resource intensive function, so use with caution on 'big' data sets

To answer your second question and using the above explanation

Aggr(DISTINCT Sum([Measure Service]),[Request ID],[Serivce ID]). Literally just add the dimension that has the logic you want to apply when summing the values you deem distinct

Cheers,

Byron