topic Re: Budget table with different granularity in QlikView

Budget table with different granularity

Mon, 16 Mar 2015 15:02:41 GMT

Hello,

I received two budgets table from two different departments of the same company.

The first one has the following fields:

Sales rep
Month
Budget

The second one has the following fields:

Sales rep
Customer number
Month
Budget

I received the first table way before the second one, and I showed the comparison between actual numbers and budget numbers like this:

Sales rep
Month
Actual
Budget

Now, I need to show the comparison between actual numbers and budget numbers on the sales rep level for the two departments, but also on the customer level, and I don't know how I can handle the missing customer field on the first table.

Would generic keys be the solution? In this case, I have different granularity on one fact table.

Re: Budget table with different granularity

hic — Mon, 16 Mar 2015 15:08:42 GMT

Yes! Generic keys would be the solution.

See more on Fact Table with Mixed Granularity

HIC

Re: Budget table with different granularity

Oleg_Troyansky — Mon, 16 Mar 2015 15:10:39 GMT

Yes, generic key is your solution in this case. The budget from the first department should be populated with Customer = *ALL* and Customer *ALL* needs to get associated with all customers in the Customers table via generic link table.

I'm describing this solution in great detail in my new book QlikView Your Business, that's coming out in a month or so...

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Re: Budget table with different granularity

Mon, 16 Mar 2015 16:41:58 GMT

Thank you for the quick answer. I've read the brief on generic keys that you provided a few times and it is still not very clear to me.

As Oleg said, I get that I should populate my table from the first department with Customer = *ALL*, but the generic key in the dimensional link table is still a bit confusing. As far as I understand, it should look like this:

%CustomerID (my generic key which should be linked to the fact table)
CustomerID (and this should be linked to my dimensional customer table)

But what should my %CustomerID key be composed of in my case? SalesRep&CustomerID, CustomerID&CustomerID, something else?

Re: Budget table with different granularity

buzzy996 — Mon, 16 Mar 2015 16:57:58 GMT

simply u can concatenate through concatenate key word in ur script and play from front end comparisons accordingly.

Re: Budget table with different granularity

Oleg_Troyansky — Mon, 16 Mar 2015 20:16:02 GMT

You should build your dimension link table in two steps:

1. Associate each "true" Customer ID to itself, only named differently - something like this:

Customer_Link:

LOAD

CustomerID,

CustomerID as %CustomerID

resident

Customers

;

2. Then, associate the value *ALL* with all Customers:

LOAD

CustomerID,

'*ALL*' as %CustomerID

resident

Customers

;

The two loads will get automatically concatenated into a single table.

The only tricky part in the UI will be comparing Budget to Actual, when the user selects specific Customers. One budget will get reduced to the appropriate values for the individual customers, and the other department's budget will still appear as a whole. You should add a warning about that.

cheers,

Oleg Troyansky

Re: Budget table with different granularity

Wed, 18 Mar 2015 17:15:02 GMT

A little late, but thank you for the answer. I finally managed to make it work.